CODEWITHSUNDEEP
mysql
visit2shobhit
visit2shobhit

Reputation: 343

Select distinct set of values values from table in MS Sql

I have table in my db which is in MSSQL having following structure

DayID      Sequence           Cities       Title     Hotels   
----------------------------------------------------------------
1                 1             Manali       Day1      Hotel A      
2                 2             Manali       Day2      Hotel B     
3                 2             Delhi        Day3      Hotel C    
4                 3             Delhi        Day4      Hotel D     
5                 3             Manali       Day5      Hotel E    
6                 4             Manali       Day6      Hotel F

Now I need The result as following

Cities
------   
Manali    
Delhi   
Manali.

I have used group by Cities but it is giving only two cities

Manali
Delhi

But I need following output.

Manali    
Delhi   
Manali

Please Suggest.

Thanks in Advance.

Upvotes: 0

Views: 165

Answers (3)

DCR
DCR

Reputation: 15698

First make sure the table is sorted so we can use the 'by' option in the data step:

 proc sort data=tablename;
 by sequence cities;
 run;

In the data step use the 'by' clause to select the first row of each group and output. Only 'keep' cities as requested by user

 data desired;
 keep cities;
 set tablename;
 by sequence cities;
 if first.cities then output;
 run;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

Try this:

SELECT Cities
FROM (
   SELECT Cities, DayID,
          ROW_NUMBER() OVER (ORDER BY DayID) -
          ROW_NUMBER() OVER (PARTITION BY Cities ORDER BY DayID) AS grp
   FROM mytable) AS t
GROUP BY Cities, grp
ORDER BY MIN(DayID)

Calculated field grp identifies islands of consecutive records having the same Cities value. Using this field in a GROUP BY clause, we can extract the required sequence of Cities values.

Note: The above query works in SQL Server.

Upvotes: 4

Nuwan Walisundara
Nuwan Walisundara

Reputation: 95

depending on requirement you can group by eg: select Cities from table group by Cities

Upvotes: 0

Related Questions