CODEWITHSUNDEEP
sql-server
Hamdi Altinok
Hamdi Altinok

Reputation: 21

Distinct and order by issue in SQL query (Easy but is it possible?)

I have a table with these columns (MS SQL-Server 2008);

city cityDate

and rows like this;

What I want is ordering the date column (desc) and getting the distinct values of city. So the resut should be;

I tried;

select distinct(city) from TableCity order by cityDate desc

but the output is;

Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Upvotes: 1

Views: 1146

Answers (2)

BizApps
BizApps

Reputation: 6130

You just need to use group by instead of distinct:

Suppose T is your Table:

WITH T as
(
SELECT 'Porto' City,'20.11.1988' CityDate UNION ALL
SELECT 'Porto' City,'19.11.1988' CityDate UNION ALL
SELECT 'Lisbon' City,'21.11.1988' CityDate
)

--TEST 1:

select City,CityDate from T GROUP BY City,CityDate Order by CityDate DESC

--Result: This still displays the three rows because City Date of Porto is not the same,but if Porto City Date is the same it will display only two rows.

City    CityDate
Lisbon  21.11.1988
Porto   20.11.1988
Porto   19.11.1988

--TEST 2: 

select T2.City 
FROM
(select City from T GROUP BY City,CityDate) as T2
GROUP BY T2.City

OR

you can use CTE:

With T as
(
select City from YourTable GROUP BY City,CityDate
)  

select City FROM T group by City

--Result:

City
Lisbon
Porto

Regards

Upvotes: 4

Mike Parkhill
Mike Parkhill

Reputation: 5551

I'm doing this on my phone so the following is untested, but it should work

SELECT T2.city
FROM ( SELECT city, MAX(cityDate) AS maxDate
                   FROM T
                 GROUP BY city
                 ORDER BY maxDate desc ) AS T2

Upvotes: 0

Related Questions