Select DISTINCT and MAX in the same query

Question

I have a table with rows/data in the following structure

-----------
| SURVEY_ID |
------------
|  1       |
|  2       |
|  2       |
|  3       |
|  4       |
-----------

I want to get the distinct IDs and the maximum id in the same query. I tried

select distinct(survey_id) as survey_id , max(survey_id) as current 
from survey_main 
group by survey_id

This doesn't seem to return the correct result. What am I missing?

Edit: Required result

        ----------------------
        | Distinct|  Max     |
        ----------------------
        |  1       |  4      |
        |  2       |  4      |
        |  3       |  4      |
        |  4       |  4      |
        ----------------------

have you tried this http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql — Mowgli, Mar 18 '13 at 20:21
Do you want the count of all survey_ids and the max (two columns, one row)? — BellevueBob, Mar 18 '13 at 20:22
@ItayMoav-Malimovka I just don't want to execute 2 separate queries for a minor thing. The max value repeating in each row is fine with me. — Vaishak Suresh, Mar 18 '13 at 20:25
See my proposal below, that does exactly what you want. Don't worry about the sub-select - usually the database caches a lot so it won't be too slow ;-) — Trinimon, Mar 18 '13 at 20:38
In this case, Distinct and Group By do the same same thing. There is no need to use both. — iGanja, Mar 18 '13 at 20:39
@Andre http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql — Itay Moav -Malimovka, Mar 18 '13 at 23:31
@Itay Moav -Malimovka: that is really an interesting point though it's not applicable to the proposal I made; Thanks for the note! — Trinimon, Mar 19 '13 at 07:00

score 8 · Accepted Answer · answered Mar 18 '13 at 20:35

8

I think Itay Moav-Malimovka's solution is just perfect, however if you really want to have two columns you could use ....

select distinct(survey_id) as identifier, 
       (select max(survey_id) from survey) as "current" 
  from survey_main;

Cheers!

answered Mar 18 '13 at 20:35

Trinimon

13,839
9
44
60

BellevueBob · Answer 2 · 2013-03-18T20:34:04.000

4

If you are after the count of all survey_ids and the max in one query, try this:

select count(distinct survey_id) as number_of_survey_ids 
     , max(survey_id) as current 
from survey_main

And if you want to add the max value to every row and your database supports window functions, try this:

select survey_id 
     , max(survey_id) over () as current 
from survey_main

edited Mar 18 '13 at 20:34

answered Mar 18 '13 at 20:26

BellevueBob

9,498
5
29
56

score 2 · Answer 3 · answered Mar 18 '13 at 20:24

2

SELECT DISTINCT *
FROM T
ORDER BY SURVEY_ID DESC

The first result is the MAX, the entire Dataset is the distinct

answered Mar 18 '13 at 20:24

Itay Moav -Malimovka

52,579
61
190
278

Isn't really what I wanted. I wanted the query to return the max, I can anyway parse the resultset manually. – Vaishak Suresh Mar 18 '13 at 20:31

Select DISTINCT and MAX in the same query

3 Answers3