nested select in sql server 2008

Question

I save user logins in a table that named loginstats, I want to retrieve last login of every users, I use these code but I meet some error, What is my mistake?

select *
from loginStats
where id in (
    select distinct username, MAX(id) as id
    from loginStats
    group by username)

What is "id" and how does it relate to "last login"? And why are you doing a sub-select? Perhaps you can explain (a) your schema and (b) what error you are getting? — Joe
– Joe, Commented Feb 27, 2012 at 19:23

Lamak · Accepted Answer · 2012-02-27 19:23:43Z

2

You are doing id IN, but are trying to compare it to multiple columns. Try this instead:

SELECT A.*
FROM LoginStats A
INNER JOIN (SELECT DISTINCT username, MAX(id) as id
            FROM loginStats
            GROUP BY username) B
ON A.username = B.username AND A.id = B.id

answered Feb 27, 2012 at 19:23

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user596075 · Accepted Answer · 2012-02-27 19:23:20Z

1

select *
from loginStats
where id in (
    select distinct MAX(id) as id
    from loginStats
    group by username)

You can't have multiple field outputs in your IN subquery.

answered Feb 27, 2012 at 19:23

user596075

Comments

Arion · Accepted Answer · 2012-02-28 12:11:48Z

1

Or you could also do this like this:

;WITH CTE
AS
(
    SELECT 
        RANK() OVER(
                    PARTITION BY loginStats.username 
                    ORDER BY loginStats.id DESC
                ) AS iRank,
        loginStats.*
    FROM 
        loginStats
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.iRank=1

Here is some information how to use the rank function and how it is applied.

Here is some information on msdn about the rank function.

Here is some information about cte function and usage

Here is some information about With clause and how it is used

Hope it helps you understand

edited Feb 28, 2012 at 12:11

answered Feb 28, 2012 at 8:57

Arion

31.3k10 gold badges74 silver badges89 bronze badges

2 Comments

Ehsan Over a year ago

Thanks, But I don't now these functions and word keys RANK(), Partition By, CTE. If you have some source that I learn from it, introduce.

Arion Over a year ago

No problem. I just think that if you understand this function you can write better queries. Maybe a +1 for the help?

Icarus · Accepted Answer · 2012-02-27 19:24:08Z

0

The mistake is that in the IN clause only one column can be specified and you are specifying two: username and id.

answered Feb 27, 2012 at 19:24

Icarus

64k14 gold badges102 silver badges116 bronze badges

Comments

bhamby · Accepted Answer · 2012-02-27 19:25:11Z

0

You're selecting two fields, where you should only return one. This correlated subquery should work for you:

select *
from loginStats AS a
where id = (
    select MAX(id) as id
    from loginStats AS b
    where b.username = a.username)

answered Feb 27, 2012 at 19:25

bhamby

15.6k1 gold badge49 silver badges67 bronze badges

Collectives™ on Stack Overflow

nested select in sql server 2008

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related