February 7, 2013 at 12:33 am
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....
February 7, 2013 at 12:41 am
Methew (2/7/2013)
i have a table with three fieldsid,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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2013 at 12:45 am
Phil Parkin (2/7/2013)
Methew (2/7/2013)
i have a table with three fieldsid,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
February 7, 2013 at 12:57 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2013 at 1:05 am
Perfect...Sir you did exactly what i want.
DONE...thanks
February 7, 2013 at 1:08 am
Methew (2/7/2013)
Perfect...Sir you did exactly what i want.DONE...thanks
Thanks for posting back - glad to help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 11, 2013 at 3:14 pm
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.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply