July 15, 2010 at 8:14 am
Hi, i want to select the last row from a user, how can i do it? for example:
john | 10
john | 90
john | 80
Luis | 20
Luis | 100
Paul | 90
Paul | 80
Paul | 100
Peter | 90
Peter | 100
Laura | 20
Laura | 50
Laura | 50
Ana | 90
Ana | 80
In this example should return in the query:
john | 80
Luis | 100
Paul | 100
Peter | 100
Laura | 50
Ana | 80
how can i do it?
July 15, 2010 at 9:12 am
I have the same question. Is that using function distinct on name ... ?
July 15, 2010 at 9:16 am
Does your table have any primary key to work on, apart from those 2 columns?
July 15, 2010 at 9:18 am
ColdCoffee (7/15/2010)
Does your table have any primary key to work on, apart from those 2 columns?
yes it have a primary key
July 15, 2010 at 9:27 am
Ok even if it doesn, try this:
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(name varchar(10), amount int )
insert into #temp
select 'john',10
union all select 'john',90
union all select 'john',80
union all select 'Luis',20
union all select 'Luis',100
union all select 'Paul',90
union all select 'Paul',80
union all select 'Paul',100
union all select 'Peter',90
union all select 'Peter',100
union all select 'Laura',20
union all select 'Laura',50
union all select 'Laura',50
union all select 'Ana',90
union all select 'Ana',80 ;
with CTe as
( select name, amount
, row_Number() over (partition by name order by (select 0)) RN
FROm #temp
),
final as
(
select name , max(rn) maxrn from cte group by name
)
select t.* from CTe t join final f
on t.name = f.name and t.rn = f.maxrn
Hope it helps!
July 15, 2010 at 9:30 am
piortasd (7/15/2010)
ColdCoffee (7/15/2010)
Does your table have any primary key to work on, apart from those 2 columns?yes it have a primary key
can u post the table structure and some sample data (including primary key) to work with ?
If it has a primary key then we dont require a join in the code i provided..
July 15, 2010 at 9:43 am
Just solved with this way, dont know if very good way, but its working
SELECT CONVERT(INT, dbo.OSUSR_BZA_USER_LOGIN_DAY.UserProfilePercentage) AS PERCENTE,
dbo.OSUSR_A7L_GROUP.Name,
dbo.OSUSR_A7L_USER_MASTER.Name
FROM dbo.OSUSR_A7L_USER_MASTER LEFT JOIN dbo.OSUSR_BZA_USER_LOGIN_DAY
ON dbo.OSUSR_A7L_USER_MASTER.Id = dbo.OSUSR_BZA_USER_LOGIN_DAY.UserMasterId LEFT JOIN dbo.OSUSR_A7L_GROUP
ON dbo.OSUSR_A7L_USER_MASTER.GroupId = dbo.OSUSR_A7L_GROUP.Id
WHERE NOT dbo.OSUSR_BZA_USER_LOGIN_DAY.UserProfilePercentage = ' '
AND dbo.OSUSR_A7L_USER_MASTER.IsActive = 'TRUE'
AND dbo.OSUSR_BZA_USER_LOGIN_DAY.ID = (SELECT TOP 1 MAX(dbo.OSUSR_BZA_USER_LOGIN_DAY.ID)
FROM dbo.OSUSR_BZA_USER_LOGIN_DAY
WHERE dbo.OSUSR_BZA_USER_LOGIN_DAY.USERMASTERID =
dbo.OSUSR_A7L_USER_MASTER.ID
)
ORDER BY PERCENTE ASC
July 19, 2010 at 1:12 pm
Your way will work, but it is not the most efficient (as you guessed.) I use to use subqueries in WHERE clauses just like you have done, but I've learned that the performance is generally horrible. Now, you may not notice this on a smaller table, but I would rather code for the eventuality that the table will grow to such a size that performance is essential.
Also, as a side note, I would highly recommend aliasing the tables in your query so it's much easier to read.
SELECT PERCENTE,
GroupName,
UserMasterName
FROM
(
SELECT CONVERT(INT, uld.UserProfilePercentage) AS PERCENTE,
GroupName = g.Name,
UserMasterName = um.Name,
RN = ROW_NUMBER() OVER(PARTITION BY uld.USERMASTERID ORDER BY uld.ID DESC)
FROM bo.OSUSR_A7L_USER_MASTER um
LEFT JOIN dbo.OSUSR_BZA_USER_LOGIN_DAY uld
ON um.Id = uld.UserMasterId
LEFT JOIN dbo.OSUSR_A7L_GROUP g
ON um.GroupId = g.Id
WHERE NOT uld.UserProfilePercentage = ' '
AND um.IsActive = 'TRUE'
) sq
WHERE RN = 1
ORDER BY PERCENTE ASC
July 19, 2010 at 10:57 pm
Hi
I created a table called User and inserted the records.
SELECT [UserName],[Data] FROM [dbo].[User]
This is the query which will give maximum value :
select UserName,MAX(Data) AS MaxValue from [User] group by UserName having MAX(Data) <> 0
Thanks
July 20, 2010 at 3:17 am
Hi,
To get distinct with max try this.
select name,MAX(amount) AS MaxValue from #temp group by name having MAX(amount) <> 0
TP get distinct with last record of each user, amount and count:
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(name varchar(10), amount int )
insert into #temp
select 'john',10
union all select 'john',90
union all select 'john',80
union all select 'Luis',20
union all select 'Luis',100
union all select 'Paul',90
union all select 'Paul',80
union all select 'Paul',100
union all select 'Peter',90
union all select 'Peter',100
union all select 'Laura',20
union all select 'Laura',50
union all select 'Laura',50
union all select 'Ana',90
union all select 'Ana',80 ;
with CTe as
( select name, amount
, row_Number() over (partition by name order by (select 0)) RN
FROm #temp
),
final as
(
select name , max(rn) maxrn from cte group by name
)
select t.* from CTe t join final f
on t.name = f.name and t.rn = f.maxrn
July 20, 2010 at 1:38 pm
Sankyverma, the request is to get the last record not the max value record for each user.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
July 20, 2010 at 11:21 pm
Hi
Try this :
SELECT [UserName]
,[Data] , ROW_NUMBER() OVER (ORDER BY [UserName]) AS ID
INTO A FROM [EmployeeDB].[dbo].[User]
SELECT [UserName] ,[Data] FROM A WHERE ID IN (
SELECT MAX(ID) AS MaxValue FROM A GROUP BY UserName HAVING MAX(ID) <> 0)
DROP TABLE A
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply