CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Dusk
Dusk

Reputation: 2201

How to get selected rows of columns between x and y

I'm trying to write a query that returns me the selected rows of the columns based on x and y row values of another column.

But the following query is returning me all the rows of the two selected columns even though I've specified a range. Could anyone please tell me what's wrong I'm doing?

select username, password 
from loginuser 
group by username, password 
having count(id) between '1' and '2'

Upvotes: 0

Views: 2170

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107796

Why your query doesn't work

select username, password 
from loginuser 
group by username, password 
having count(id) between '1' and '2'

Note: COUNT() returns an int. SQL Server will convert your quoted (next time, please don't) '1' and '2' and make them the integer values 1 and 2.

The query means:

  • FROM: get data from table loginuser
  • GROUP BY: for every DISTINCT set of username, password, group them together
  • SELECT: during the grouping, retrieve the columns username, password and calculate count(id) which is how many rows there are with that SAME username and password
  • HAVING: using the counts produced, limit the results to only those where the unique combination of username+password appears EXACTLY once or twice in the table

What did you want?

The first two distinct username and password combinations, in no particular order?

select TOP 2 username, password 
from loginuser 
group by username, password

The latest two rows for each distinct username + password combination in the table loginuser? (you need the PARTITION BY clause)

select username, password 
from
(
  select *, rn=row_number() over (
    partition by username, password order by id desc)
  from loginuser
) X
WHERE rn between 1 and 2

Upvotes: 1

marc_s
marc_s

Reputation: 755207

The HAVING operator is to be applied to aggregate functions - if you want to pick those rows where a SUM exceeds a certain value or something.

You just want a regular selection - use a WHERE clause for that:

SELECT username, password 
FROM dbo.loginuser 
WHERE id BETWEEN 1 and 2

This will select all rows where the value of ID is between 1 and 2.

If you want to select by row number, ordered by ID - you need something like this:

;WITH RowNumberValues AS
(
   SELECT username, password,
          ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNum' 
   FROM dbo.loginuser 
)
SELECT username, password
FROM RowNumberValues
WHERE RowNum BETWEEN 1 AND 2

If you don't have any SUM or COUNT in your query, you shouldn't be using a GROUP BY either...

Is that what you're looking for?? Based on your question and query, it's not entirely clear what you're really looking for.... can you describe in more detail what you want to select'?

Upvotes: 2

Related Questions