lost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

lost

Author

Topic

cgxavier
Starting Member

5 Posts

Posted - 2008-01-17 : 13:39:59

I get multiples of one record. i dont want multiples. but i need to have all the records that are the latest and i dont know how. can you help me.
thanks

SELECT TOP 1 [sn].[WrkstaId]
,[sn].[Computer Model]
,[sn].[Serial Number]
,[sn].[Asset Tag]
,[sn].[Computer Type]
,[id].[Name]
,[id].[Domain]
,[id].[OS Name]
,[id].[OS Type]
,[id].[Last Logon User]
,[id].[Last Logon Domain]
,[id].[Client Date]
,[pu].[_id]
,[pu].[Month]
,[pu].[User]
FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id
ON [sn].[WrkstaId]=[id].[WrkstaId]
INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Primary_User] pu
ON [sn].[WrkstaId]=[pu].[WrkstaId]
WHERE [sn].[WrkstaId]=(SELECT MAX([WrkstaId]) FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number]
WHERE [Serial Number]='68659')
ORDER BY [pu].[_id] DESC

68659 Latitude D620 28LGGD1 Data not accessible Portable WL-28LGGD1 AZCORP Microsoft Windows 2000 Professional uswclh04 AZCORP 2008-01-16 17:20:37.000 864649 January uswclh04

68659 Latitude D620 28LGGD1 Data not accessible Portable WL-28LGGD1 AZCORP Microsoft Windows 2000 Professional uswclh04 AZCORP 2008-01-16 17:20:37.000 864648 December uswclh04

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-17 : 14:00:20

If you are getting multiples, you might want to try SELECT DISTINCT

cgxavier
Starting Member

5 Posts

Posted - 2008-01-17 : 16:38:47

THANK YOU
I NEED TO BE ABLE TO SEE ALL THE RECORD AT ONCE WITH OUT ANY DUPLICATES. I CAN GET ONE RECORD OR ONE REOCORD WITH DUPLICATES. BUT NOT ALL OF THE RECORDS THAT ARE THE NEWEST AT ONCE.

cgxavier
Starting Member

5 Posts

Posted - 2008-01-17 : 16:52:32

THANK YOU
I NEED TO BE ABLE TO SEE ALL THE RECORD AT ONCE WITH OUT ANY DUPLICATES. I CAN GET ONE RECORD OR ONE REOCORD WITH DUPLICATES. BUT NOT ALL OF THE RECORDS THAT ARE THE NEWEST AT ONCE. Using this

SELECT TOP 1 [sn].[WrkstaId]
,[sn].[Computer Model]
,[sn].[Serial Number]
,[sn].[Asset Tag]
,[sn].[Computer Type]
,[id].[Name]
,[id].[Domain]
,[id].[OS Name]
,[id].[OS Type]
,[id].[Last Logon User]
,[id].[Last Logon Domain]
,[id].[Client Date]
,[pu].[_id]
,[pu].[Month]
,[pu].[User]
FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id
ON [sn].[WrkstaId]=[id].[WrkstaId]
INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Primary_User] pu
ON [sn].[WrkstaId]=[pu].[WrkstaId]
WHERE [sn].[WrkstaId]=(SELECT MAX([WrkstaId]) FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number]
WHERE [Serial Number]='68659')
ORDER BY [pu].[_id] DESC

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-17 : 16:56:00

how are you getting more than one row back with TOP 1 in your query?

how about posting:

1. the DDL for your tables
2. some insert statements with sample data
3. expected results based on sample data

finally, are you celebrating CAPS LOCK DAY early?

elsasoft.org

cgxavier
Starting Member

5 Posts

Posted - 2008-01-17 : 17:13:23

sorry i didnt explain myself very well. i dont get more than one record with top1, i need to get all the reocrds in the tables without the duplcates. i will send whats need it.

cgxavier
Starting Member

5 Posts

Posted - 2008-01-18 : 16:45:17

how do i get more than just one record using this

SELECT TOP 1 [sn].[WrkstaId]
,[sn].[Computer Model]
,[sn].[Serial Number]
,[sn].[Asset Tag]
,[sn].[Computer Type]
,[id].[Name]
,[id].[Domain]
,[id].[OS Name]
,[id].[OS Type]
,[id].[Last Logon User]
,[id].[Last Logon Domain]
,[id].[Client Date]
,[pu].[_id]
,[pu].[Month]
,[pu].[User]
FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id
ON [sn].[WrkstaId]=[id].[WrkstaId]
INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Primary_User] pu
ON [sn].[WrkstaId]=[pu].[WrkstaId]
WHERE [sn].[WrkstaId]=(SELECT MAX([WrkstaId]) FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number]
WHERE [Serial Number]='68659')
ORDER BY [pu].[_id] DESC

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-18 : 19:16:43

so far you've shown that you know how to copy/paste your own posts, and then hit the "submit reply" button.

how about now you perform steps 1-3 of my post above? then we'd have a chance at helping you.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources