How group rows with a sorted?

Question

Here is my query:

SELECT * 
FROM `recordstouser` 
GROUP BY IdUserRecordstouser 
ORDER BY IdRecordstouser DESC;

In a table there is a field StatusRecordstouser which can take values 0/1. So I need get grouped rows by IdUserRecordstouser such as at grouped row will be a row wtih StatusRecordstouser = 0;

For example:

id | StatusRecordstouser | IdUserRecordstouser
1    1                     2
2    1                     2
3    0                     2
4    0                     3

Output 2 row:

4 | 0 | 3
3 | 0 | 2

The most correct version:

SELECT * FROM ( SELECT * FROM `recordstouser` WHERE StatusRecordstouser = 0 ) t 
JOIN detailtousers ON detailtousers.idDetailToUsers = t.IdUserRecordstouser 
WHERE t.IdtoUserRecordstouser = 458 
GROUP BY t.IdUserRecordstouser ORDER BY t.id DESC

Error:

Unknown column 't.IdtoUserRecordstouser' in 'where clause'

what about this: SELECT * FROM FROM `recordstouser` ORDER BY IdUserRecordstouser DESC — , Nov 08 '14 at 18:22
Who have set me minus point? If you have nothing to do more - leave the site — AhmedFaud, Nov 08 '14 at 18:23
Please, look at question scheme, you give me not correct examplesю I did not set to you minus. I am sure — AhmedFaud, Nov 08 '14 at 18:25
No one hates you: your question seems too difficult because it is not clear what you are asking. No one understands what you want to do. Take time to reedit your question. — , Nov 08 '14 at 18:27
Ahmed: you can edit your question even if you have no points. — , Nov 08 '14 at 19:47
@AhmedFaud your question just doesn't make sense. You've said you need all rows with 0 or 1 but why doesn't your result set include anything with a 1? Do you mean to say that if there is no row with a status 0, show a row with status 1 instead? — AdamMc331, Nov 08 '14 at 22:12

score 3 · Answer 1 · edited Nov 08 '14 at 17:59

3

Group function: use MAX(), MIN() MySQL.

Second case:

SELECT *
FROM (SELECT * FROM `recordstouser` GROUP BY IdUserRecordstouser) as data
ORDER BY IdRecordstouser;

edited Nov 08 '14 at 17:59

answered Nov 08 '14 at 16:50

Ilya Isaev

197
6

Does not work correct this query, it return me row wuth a status = 1 – AhmedFaud Nov 08 '14 at 18:04

Gordon Linoff · Answer 2 · 2014-11-08T20:18:37.007

Why do you need group by? Just use where:

SELECT * 
FROM `recordstouser`
WHERE IdRecordstouser = 0;

EDTI:

So, you want a preference for 0, if that row is available.

You can do this as:

SELECT *
FROM recordstouser rtu
WHERE IdRecordstouser = 0
UNION ALL
SELECT *
FROM recordstouser rtu
WHERE NOT EXISTS (SELECT 1
                  FROM recordstouser rtu2
                  WHERE rtu2.IdUserRecordstouser = rtu.IdUserRecordstouser AND rtu2.IdRecordstouser = 0
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM recordstouser rtu2
                  WHERE rtu2.IdUserRecordstouser = rtu.IdUserRecordstouser AND rtu2.Id > rt.Id
                 );

This assumes that there are other columns in the table. For your data (and reasonable variants thereof), this would work:

SELECT id, StatusRecordstouser, IdUserRecordstouser
FROM recordstouser rtu
WHERE IdRecordstouser = 0
UNION ALL
SELECT MAX(id), 1, IdUserRecordstouser
FROM recordstouser rtu
GROUP BY rtu.IdUserRecordstouser
HAVING SUM(IdRecordstouser = 0) = 0;

WHERE can not use, I need get all rows with a 0/1, but if 0 - show it at first — AhmedFaud, Nov 08 '14 at 17:51

score 1 · Accepted Answer · answered Nov 08 '14 at 19:48

1

Try this. I know you said you can't use WHERE, but this is a bit different. You can see the results here: http://sqlfiddle.com/#!2/8b5a0/11

SELECT *
FROM 
(
  SELECT * 
  FROM `recordstouser`
  WHERE StatusRecordstouser = 0
) t
GROUP BY t.IdUserRecordstouser
ORDER BY t.id DESC;

You could also ORDER BY t.StatusRecordstouser ASC instead of ORDER BY t.id DESC to get 0's first.

answered Nov 08 '14 at 19:48

myahl

70
2
7

Are you trying to join the results of this query to another table? – myahl Nov 08 '14 at 22:38

score 1 · Answer 4 · edited May 23 '17 at 11:45

Your question seems to me that you only want to show rows where the 'statusrecordstouser' column is 0 (for each user) but if that does not exist, show one where 'statusrecordstouser' is 1. If that is correct, consider something like this.

SELECT MAX(id), statusrecordstouser, iduserrecordstouser
FROM myTable
GROUP BY statusrecordstouser, iduserrecordstouser
ORDER BY iduserrecordstouser, statusrecordstouser;

This will return two rows if the user has a 0 and a 1, or one row if they only have one or the other. I have ordered to put them in an order that matches what you want (0 row will always go first). I used the max to get the latest row if a group occured more than once.

To show what I mean, this is what the output of the above query is:

| id | status | idUser |
+----+--------+--------+
| 3  |   0    |   2    |
+----+--------+--------+
| 2  |   1    |   2    |
+----+--------+--------+
| 4  |   0    |   3    |
+----+--------+--------+

Now, the question is how do you differentiate between the two rows? Well, you don't have to, you want to just take the first from each grouping because if it's 0 you'll take it, and if there is no 0 then it will be 1 and you can take that too. Please see this question for how to get the first item from a group.

The query looks something like this:

SELECT t.id, t.statusrecordstouser, t.iduserrecordstouser
FROM(SELECT MAX(id) AS id, statusrecordstouser, iduserrecordstouser
    FROM myTable
    GROUP BY statusrecordstouser, iduserrecordstouser
    ORDER BY iduserrecordstouser, statusrecordstouser
    ) t
WHERE(SELECT COUNT(*)
      FROM(SELECT MAX(id) AS id, statusrecordstouser, iduserrecordstouser
          FROM myTable
          GROUP BY statusrecordstouser, iduserrecordstouser
          ORDER BY iduserrecordstouser, statusrecordstouser
      ) tt
      WHERE tt.iduserrecordstouser = t.iduserrecordstouser AND tt.statusrecordstouser <= t.statusrecordstouser
) <= 1;

I tested this in SQL Fiddle and it worked. I even added a row that only had status 1 to make sure it worked.

How group rows with a sorted?

4 Answers4