SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 One record per ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/16/2007 :  10:44:26  Show Profile  Reply with Quote
Hi,

I have a table which has multiple records per id:
declare @a table (id int, nme varchar(10))
insert	@a
select	1, 'nme 1' union all
select	1, 'nme 1a' union all
select	2, 'nme 2' union all
select	2, 'nme 2a' union all
select	2, 'nme 2b' union all
select	3, 'nme 3' union all
select	4, 'nme 4'

How could I extract one record per id from it? I can do
select id, max(nme)
from @a
group by id
, but I have million other columns in this table, and no PK - how do I include all those other columns?

To be clear - I don't have to have records with max(nme). I only need sample data, with one record per id.

TIA

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/16/2007 :  10:49:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If you need only one, why cant be Min or Max of that value?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/16/2007 :  11:58:59  Show Profile  Reply with Quote
quote:
If you need only one, why cant be Min or Max of that value?
Yes, it can. But the real table has over 80 columns, not 2 - how do I write the query? List all the fields but one in the group by clause with max() or min()? The result might not make sense - I'd like to have one of the original records, not a list of max or min values for each id.

Let's say my table looks as follows:
declare @a table (id int, nme varchar(10), idA int, prev_v int, nxt_v int)
insert	@a
select	1, 'nme 1',  1, 5, 3 union all
select	1, 'nme 1a', 2, 2, 4 union all
select	2, 'nme 2',  2, 5, 10 union all
select	2, 'nme 2a', 1, 2, 3 union all
select	2, 'nme 2b', 2, 2, 11 union all
select	3, 'nme 3',  3, 1, 7 union all
select	4, 'nme 4',  2, 2, 5
How do I write the query to select one record per id, with all columns? If I were to use{code]select id, max(nme), max(idA), max(prev_v), max(nxt_v) from @a
group by id
1     'nme 1a'    2       5        4
- there is no such record in the source table.

TIA
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 04/16/2007 :  12:19:51  Show Profile  Visit sshelper's Homepage  Reply with Quote
If the values in your nme column is unique per ID, you can do the following:

SELECT A.*
FROM @a A INNER JOIN (SELECT ID, MIN(NME) AS NME FROM @a GROUP BY ID) B
ON A.ID = B.ID AND A.NME = B.NME


SQL Server Helper
http://www.sql-server-helper.com
Go to Top of Page

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/16/2007 :  13:25:24  Show Profile  Reply with Quote
quote:
SELECT A.*
FROM @a A INNER JOIN (SELECT ID, MIN(NME) AS NME FROM @a GROUP BY ID) B
ON A.ID = B.ID AND A.NME = B.NME
Thanks - I guess I'll have to find a column with unique values.

Isn't there a way to fetch one record per id without having to have another column with unique values? Or maybe better - can such column be generated in the query? I remember that Peso once wrote a query for me, where he added column SEQ, which was sequence number for record within the group. I can't seem to adapt his solution here though, because I don't have date field with reliable values. Any ideas?

TIA
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/16/2007 :  13:59:18  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>> Thanks - I guess I'll have to find a column with unique values.

So, are you saying that this table doesn't have a primary key? Why not? EVERY table in SQL Server should always have a primary key, otherwise it is difficult, inefficient, and/or impossible to write valid and accurate SQL statements against those tables.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/17/2007 :  12:03:06  Show Profile  Reply with Quote
quote:
So, are you saying that this table doesn't have a primary key? Why not?
There is common sense, and then there is real world .

Actually, there is no PK, as this is staging table, populated from other systems. Final table has composite PK, and it's much easier to bring partial records in and resolve missing columns later.

Regardless of the design, I was only wondering if such "sequence" column could be generated manually. But it's not a show stopper, if there isn't any easy way to do it, then I'll live without it .
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  12:41:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
What do you mean by this:

>> Final table has composite PK, and it's much easier to bring partial records in and resolve missing columns later.

When I ask "what is the primary key of this table", I am not saying that it needs to be 1 single column; as you know, a table's primary key can be multiple columns. Do multiple columns in this data define a PK?

BTW -- Even staging import tables should have at least IDENTITY primary key columns. Even in the "real world" . You may need to copy the raw staging table into a "real" table with the identity if that's your only option, and once that is done you can access that "real" table with a true primary key and you are good to go. But more likely, a set of columns (hopefully) in this data should uniquely identify each row. Otherwise, you should really see if you can edit the export process on the other system to export all PK columns, if possible.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/19/2007 :  10:05:37  Show Profile  Reply with Quote
Thanks - I'm sure that's great advice. Source table in my case does not have PK - composite or not. Thanks anyway.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/19/2007 :  10:09:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by ch9862
I'm sure that's great advice.



Translation: I am ignoring your advice.

Oh well.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/19/2007 :  10:34:14  Show Profile  Reply with Quote
I don't understand why you can't just add a identity primary key to your staging table.

There is obviously a very good reason to have it there, so what is the reason for not putting it there?



CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/19/2007 10:37:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000