picking only one row from results of select command

CPSRI
CPSRI used Ask the Experts™
on
Hi,
My select command is returning multiple rows with same values, because the table is like that but I want to pick only one value from a same group of values. In clear, I have a table with columns city, zip, state...which will have same state names but different in city and zip values. If I use select command like this 'select state from mytable' it will return all rows, but I want only one value for one state. How can I do this? Please help me to do this
Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
select distinct state from mytable

Commented:
Use this:

Select DISTINCT state from mytable
Jagdish DevakuSr DB Architect

Commented:
u can use distinct...

which will get you unique data results.

Select distinct state from mytable

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:
SELECT city, zip, state
FROM (
SELECT city, zip, state, row_number() OVER ( partition BY state ORDER BY city) rnum
FROM ur_table) temp
WHERE rnum = 1

Open in new window


Use this
SELECT state FROM mytable GROUP BY city, zip

Open in new window

Top Expert 2012

Commented:
Anyone else want to suggest using DISTINCT? :)
Jagdish DevakuSr DB Architect

Commented:
Please let us know if you face any issues with the above suggestions.
 
 Thanks.
 

Author

Commented:
thank you very much qtworek.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial