Solved

Help with SQL - Return 1 complete record for each group

Posted on 2015-01-09
4
116 Views
Last Modified: 2015-01-09
Hi..
I need to return 1 record , a sample record for each state in a table. Table has millions of records.
Table as a state field.

I need 1 single record for each state with all the fields..

table looks something like this

name, address, city, STATE, field1, field2, field3...
0
Comment
Question by:JElster
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 125 total points
ID: 40540756
assuming ID is the primary key

select * from yourTable where ID in  (  SELECT MIN(ID) from yourTable GROUP BY STATE )
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 40540762
SELECT name, address, city, STATE, field1, field2, field3, ...
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY state ORDER BY city) AS row_num
    FROM table_name
) AS derived
WHERE
    row_num = 1
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 125 total points
ID: 40540765
is there a primary key / rownumber on the table?

if so you can do something like:

--find the max row for each state.
select max(id) id, state
into #temp
from <table>
group by state

then:
select * from
table a
join #temp t on a.ID = t.ID



If not I guess it would work with Name, but not sure if you allow duplicate names in a state.
0
 
LVL 1

Author Closing Comment

by:JElster
ID: 40540824
no primary key

thx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now