Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SQL - Return 1 complete record for each group

Posted on 2015-01-09
4
Medium Priority
?
138 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 500 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

916 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