Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select latest records Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:54 AM
Points: 172, Visits: 450
Hi,

I am having table which has no primary key.
eg. tablename = tblCustomer

columns firstnale,lastname,dateadded.

here for single customer multiple entries are there. Now I want to select each customer single time with max dateadded.

How can i do this?


Thanks
Abhas.
Post #1533006
Posted Tuesday, January 21, 2014 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
Use ROW_NUMBER().

If you can knock up a CREATE TABLE and a few INSERTs, someone will show you how.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1533007
Posted Tuesday, January 21, 2014 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 12,744, Visits: 31,081
create a new table with the appropriate primary key or unique constraint.
insert into that new table grouping by the the same appropriate primary key or unique constraint criteria from the step above.

since the original table did not have any primary key, i guess you don't have to worry about real foreign keys, but if there were any implied foreign keys, you'd want to update related data to point to the new table.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1533009
Posted Tuesday, January 21, 2014 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 10:17 AM
Points: 5, Visits: 15
Hi,
You can simply do a group by statement, unless I have misunderstood your request. See below example -

SELECT FirstName, LastName, MAX(DateCreated)
FROM dbo.Customer
GROUP BY FirstName, LastName

Post #1533010
Posted Tuesday, January 21, 2014 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:54 AM
Points: 172, Visits: 450


Thanks All,
I am doing the same.

SELECT
ROW_NUMBER() OVER (PARTITION BY t.CustomerName ORDER BY t.DateAdded DESC) AS RowNumber

from tblCustomer. but it is giving 1,2,3.....RowNumber.
I want again repeat RowNumber to each CustomerName.


Thanks
Abhas.
Post #1533014
Posted Tuesday, January 21, 2014 6:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:54 AM
Points: 172, Visits: 450
Thanks nicol,

very pretty solution. :).



Thanks
Abhas.
Post #1533025
Posted Tuesday, January 21, 2014 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
abhas (1/21/2014)


Thanks All,
I am doing the same.

SELECT
ROW_NUMBER() OVER (PARTITION BY t.CustomerName ORDER BY t.DateAdded DESC) AS RowNumber

from tblCustomer. but it is giving 1,2,3.....RowNumber.
I want again repeat RowNumber to each CustomerName.


Thanks
Abhas.


This code snippet looks correct - can you post the whole query? There may be something not visible in this context.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1533028
Posted Tuesday, January 21, 2014 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 14,802, Visits: 27,280
I've got a number of examples on how to "get latest" rows in this article on versioned data.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1533072
Posted Tuesday, January 21, 2014 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 14,802, Visits: 27,280
But, question, why no primary key? The vast majority of tables absolutely should have a primary key. Just as the vast majority of tables should have a clustered index (and they don't need to be the same column(s)).

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1533076
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse