Select Distinct last records

Question

Select Distinct last records

MuhammadShafiq

SSCrazy

Points: 2509
More actions
February 7, 2013 at 12:33 am

#268286

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....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 4

Perfect...Sir you did exactly what i want.

DONE...thanks

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

Methew (2/7/2013)
Perfect...Sir you did exactly what i want.
DONE...thanks

Thanks for posting back - glad to help.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MMartin1 One Orange Chip Points: 27771 More actions · Answer 6

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.

----------------------------------------------------