Solved

Help with SQL - Return 1 complete record for each group

Posted on 2015-01-09
4
121 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:
Scott Pletcher 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 40

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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