Group By but select exact record if exist

Question

I have a query:

SELECT * FROM wp_silumos_suvartojimas 
WHERE katiline_name = 'Dukstynos mikrorajonas' 
GROUP BY kainam2 
ORDER BY kainam2 ASC 
LIMIT 10

Results grouped:

Results not grouped:

UPDATED:

Group my resultset by a specific field (kainam2), and where a record from within the group has a specific value of adresas, then return that record for the group; where no record from within the group has the specified value of adresas, then return a random record for the group;

As you can see rows with ID's 22 and 164 have same kainam2 values. But in grouped results I'm getting only 1 row and I want to control which one. I want to match it against column adresas: if $some_var = adresas

Your question *doesn't* make sense (at least not to me). Editing your question to include sample data and desired results would really help. — Gordon Linoff, Apr 07 '16 at 11:59
The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh, Apr 07 '16 at 12:01
I think you only need `ORDER BY` clause as you are not doing any aggregation and just want to project rows in group. — Ambrish, Apr 07 '16 at 12:26
I only need 5 rows and these rows should be with diferent kainam2 column values. — RomkaLTU, Apr 07 '16 at 12:28
SELECT kainam2, min(id) as min_id, max(id) as max_id FROM wp_silumos_suvartojimas WHERE katiline_name = 'Dukstynos mikrorajonas' GROUP BY kainam2 ORDER BY kainam2 ASC LIMIT 10 Try this query and the try to use it to get only those rows you are interested in. — Ambrish, Apr 07 '16 at 12:30
I know this but it will not show me what i want. In grouped results I want to show row with ID 164, it should depends on user submited $some_var which is column adresas. — RomkaLTU, Apr 07 '16 at 12:35
I'm guessing you're using MySQL. Most RDBMSs wouldn't accept your query as valid SQL since you can't GROUP BY like you're doing, but MySQL has [an extension](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) that returns non-deterministic results. That's why your GROUP BY is eliminating id 164. As far as I'm aware, there isn't any way to control what it eliminates. That's why it's bad. If you want some control, I believe you'd have to use on of the [greatest N per group](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) answers. — Bacon Bits, Apr 07 '16 at 12:52
Yes it's mysql and I'm almost gave up now, I think I need to do it with some more selects. — RomkaLTU, Apr 07 '16 at 12:57
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Apr 07 '16 at 14:31
*"where a record from within the group has a specific value of adresas, then return that record for the group"* -- this is not how `GROUP BY` works. It doesn't return rows from the table. It computes each of the expressions from the `SELECT` clause using only the data specified in that expression. `SELECT * ... GROUP BY` is not even correct `SQL`. `MySQL` accepted it before version 5.7.5 but even then, [the data it returned was indeterminate](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html). Apart from the random part, your request can probably be implemented using a `JOIN`. — axiac, Apr 07 '16 at 20:04
@axiac I understood this already from Mark Bannister answer. But this sql not working as expected, obviously NOT EXIST will not return adresas = $some_var — RomkaLTU, Apr 07 '16 at 20:09
@RomkaLTU I'm afraid I cannot help you with Mark Bannister's answer. `NOT EXISTS` and nested queries are some areas of `MySQL` I never used because most of the times a simpler query using `LEFT JOIN` and the appropriate conditions solves the problem (and runs faster). — axiac, Apr 07 '16 at 20:17
@axiac and no chance you can post alternative code snippet? I don't care how this to get these results, JOINS or sub selects. — RomkaLTU, Apr 07 '16 at 20:19

score 0 · Accepted Answer · 2016-04-08T07:01:37.617

0

If you want to:

group your resultset by a specific field (kainam2), and
- where a record from within the group has a specific value of adresas, then return that record for the group;
- where no record from within the group has the specified value of adresas, then return a random record for the group;

- you can do so in MySQL by excluding all records from the resultset that do not have the specified value but are in a group where a record exists that does have the specified value.

Like so:

SELECT * FROM wp_silumos_suvartojimas s
WHERE s.katiline_name = 'Dukstynos mikrorajonas' and 
(s.adresas = $some_var or
 not exists
 (select 1 FROM wp_silumos_suvartojimas e 
  WHERE e.katiline_name = 'Dukstynos mikrorajonas' and
        e.kainam2 = s.kainam2 and
        e.adresas = $some_var) )
GROUP BY kainam2 
ORDER BY kainam2 ASC 
LIMIT 10

edited Apr 08 '16 at 07:01

answered Apr 07 '16 at 15:25

This is exactly what I'm want to do and going to try it now and update status. – RomkaLTU Apr 07 '16 at 19:40
Something very close but not working as expected this SQL didn't returning row with e.adresas = $some_var. So if i do e.adresas = 'Anykščių g. 35' then both rows with ID 164 and 22 are gone. – RomkaLTU Apr 07 '16 at 19:53
@RomkaLTU: I've just realised that there was a logical error in my query - I have amended it, so that it should work now. – Apr 08 '16 at 07:02
Yep this seems more clever way then my double sub select. – RomkaLTU Apr 08 '16 at 10:29

score 0 · Answer 2 · answered Apr 07 '16 at 23:49

Ok so i found solution, don't know how stupid it is but it works. Based on @Mark Bannester suggestion.

SELECT * FROM wp_silumos_suvartojimas s 
 WHERE s.katiline_name = 'Dukstynos mikrorajonas' 
 AND NOT EXISTS
 (SELECT 1 FROM wp_silumos_suvartojimas e
 WHERE e.katiline_name = 'Dukstynos mikrorajonas' AND
 e.kainam2 = s.kainam2 AND
 e.adresas = 'Anykščių g. 35')
 OR EXISTS
 (SELECT 1 FROM wp_silumos_suvartojimas e
 WHERE e.katiline_name = 'Dukstynos mikrorajonas' AND
 e.kainam2 = s.kainam2 AND
 s.adresas = 'Anykščių g. 35')
 GROUP BY kainam2
 ORDER BY kainam2 ASC
 LIMIT 10

Group By but select exact record if exist

2 Answers2