Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Select Distinct last records
Add to briefcase

Select Distinct last records Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Methew
Posted Thursday, February 07, 2013 12:33 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, February 23, 2013 4:35 AM
Points: 140, Visits: 243
i have a table with three fields
id,bank_id,amount

it has alot of data....how i can select last entry for all distinct banks ?
lets say(Below code has not tested)
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
Id INT IDENTITY(1,1) ,
bank_id INT,
amount BIGINT
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(Id, bank_id,amount )
SELECT '1','001','2000' UNION ALL
SELECT '2','002','1000' UNION ALL
SELECT '3','003','6000' UNION ALL
SELECT '2','002','100' UNION ALL
SELECT '2','002','500' UNION ALL
SELECT '1','001','900'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1

i need this result
1 001 900
2 002 500
3 003 6000

please give me a query which can run in mysql and sqlserver both....
Post #1416835
Phil Parkin
Posted Thursday, February 07, 2013 12:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665
Methew (2/7/2013)
i have a table with three fields
id,bank_id,amount

it has alot of data....how i can select last entry for all distinct banks ?
lets say(Below code has not tested)
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
Id INT IDENTITY(1,1) ,
bank_id INT,
amount BIGINT
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(Id, bank_id,amount )
SELECT '1','001','2000' UNION ALL
SELECT '2','002','1000' UNION ALL
SELECT '3','003','6000' UNION ALL
SELECT '2','002','100' UNION ALL
SELECT '2','002','500' UNION ALL
SELECT '1','001','900'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1

i need this result
1 001 900
2 002 500
3 003 6000

please give me a query which can run in mysql and sqlserver both....





First off, this is a SQL Server forum, not MySQL.

Second, your data does not appear to have any order, therefore the concept of 'last' is undefined. Does your real data perhaps have an ID or date which allows us to identify the order?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1416837
Methew
Posted Thursday, February 07, 2013 12:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, February 23, 2013 4:35 AM
Points: 140, Visits: 243
Phil Parkin (2/7/2013)
Methew (2/7/2013)
i have a table with three fields
id,bank_id,amount

it has alot of data....how i can select last entry for all distinct banks ?
lets say(Below code has not tested)
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
Id INT IDENTITY(1,1) ,
bank_id INT,
amount BIGINT
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(Id, bank_id,amount )
SELECT '1','001','2000' UNION ALL
SELECT '2','002','1000' UNION ALL
SELECT '3','003','6000' UNION ALL
SELECT '2','002','100' UNION ALL
SELECT '2','002','500' UNION ALL
SELECT '1','001','900'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1

i need this result
1 001 900
2 002 500
3 003 6000

please give me a query which can run in mysql and sqlserver both....





First off, this is a SQL Server forum, not MySQL.

Second, your data does not appear to have any order, therefore the concept of 'last' is undefined. Does your real data perhaps have an ID or date which allows us to identify the order?


Ok i need SqlServer Query Only.
The first Id field is autoincremented and Unique(i have not set it in above code but it is)
INSERT INTO #mytable1
( bank_id,amount )
SELECT '001','2000' UNION ALL
SELECT '002','1000' UNION ALL
SELECT '003','6000' UNION ALL
SELECT '002','100' UNION ALL
SELECT '002','500' UNION ALL
SELECT '001','900'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1
i need this result
bank_id amount
001 900
002 500
003 6000
Post #1416839
Phil Parkin
Posted Thursday, February 07, 2013 12:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665
OK, I fixed up your sample data to use a proper IDENTITY PK and not to try and insert varchars into the INT bank_id column. Solution below:

IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
Id INT IDENTITY(1,1) primary key,
bank_id INT,
amount BIGINT
)

INSERT INTO #mytable1
(bank_id,amount )
SELECT 1,'2000' UNION ALL
SELECT 2,'1000' UNION ALL
SELECT 3,'6000' UNION ALL
SELECT 2,'100' UNION ALL
SELECT 2,'500' UNION ALL
SELECT 1,'900';

with OrderedRows
as (
	select *
		,rn = row_number() over (
			partition by bank_id order by id desc
			)
	from #mytable1
	)
select id
	,bank_id
	,amount
from OrderedRows
where rn = 1
order by bank_id

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1416842
Methew
Posted Thursday, February 07, 2013 1:05 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, February 23, 2013 4:35 AM
Points: 140, Visits: 243
Perfect...Sir you did exactly what i want.

DONE...thanks
Post #1416847
Phil Parkin
Posted Thursday, February 07, 2013 1:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665
Methew (2/7/2013)
Perfect...Sir you did exactly what i want.

DONE...thanks


Thanks for posting back - glad to help.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1416850
mmartin1
Posted Monday, February 11, 2013 3:14 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 198, Visits: 657
Depending on your business requirements you may instead want to apply the above logic using a time stamp column, which you would need to add.
Post #1418651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.