How to return the first row of each group of rows

I have a group of rows that grouped by host and user. I want to return the first row of each group of hosts. I have struggled with this all day and need some help. With this T-SQL I have built the table:

USE Test

GO

IF OBJECT_ID(N'[dbo].Sample') IS NOT NULL

DROP TABLE [dbo].Sample

GO

-- Create the Sample table

CREATE TABLE [dbo].Sample(

ID [int] IDENTITY NOT NULL,

Host [varchar](128) NOT NULL,

Username [varchar](128) NOT NULL,

Seconds [int] NOT NULL)

GO

SET NOCOUNT ON

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Smith',120012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Jone',100012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Cole',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Lee',70012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Lee',8012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Cole',5012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Smith',4012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Doe',90012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Lee',80012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Cole',7012);

INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Smith',60012);

SET NOCOUNT OFF

I want to write a select statement that will return the first row of each host group:

C001 Smith 120012

C002 Lee 8012

C003 Doe 90012

Thanks for pointing me in the right direction.

Ken

Give a look to the ROW_NUMBER() function in books online; partition by host and order by seconds.|||

Try:

select

a.*

from

dbo.Sample as a

where

not exists(

select

*

from

dbo.Sample as b

where

b.Host = a.Host

and b.[ID] < a.[ID]

)

go

-- 2005

;with cte

as

(

select

Host, Username, Seconds,

row_number() over(partition by Host order by [ID]) as rn

from

dbo.Sample

)

select

*

from

cte

where

rn = 1

go

AMB

|||

Also,

select

a.*

from

dbo.Sample as a

where

a.[ID] = (

select

min(b.[ID])

from

dbo.Sample as b

where

b.Host = a.Host

)

go

AMB

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to return only the first row....

Is there a way to return only the first row of a multi-row query. In other words... I have a query returning multiple rows but I want it to stop after it retrieves the first row.
ThanksIn Oracle you would add

AND ROWNUM = 1

to the WHERE clause

-- rajXesh|||Thanks rajXesh!|||Is there a similar command for SQL Server?|||Originally posted by acg_ray
Is there a similar command for SQL Server?

SELECT TOP 1 * FROM [table]|||Thx Joel. That did it.

How to return one xml doument per row

When I do something like "select * from customer for XML auto" I get a single
string containing the xml for all customers in the customer table. I want one
xml string (i.e., document) per row so I can process one document at a time.
Is this possible?
Use FOR XML Explicit. This query should be written in a specific way.
something like this:
select 1 as Tag,
null as Parent,
customers.customerID as [Customer!1!CustomerID!id]
from customers
for xml explicit
Please refer to "EXPLICIT mode" in Books online.
Regards,
Deepak
[I Code, therefore I am]
"ACROWN" wrote:

> When I do something like "select * from customer for XML auto" I get a single
> string containing the xml for all customers in the customer table. I want one
> xml string (i.e., document) per row so I can process one document at a time.
> Is this possible?
|||FOR XML (both auto and explicit mode) is a rowset to XML aggregator. In SQL
Server 2000, this aggregator can only aggregate all the rows.
In SQL Server 2005, you will be able to write something along the lines of
Create table t (id int, name varchar(5))
go
insert into t values (1, 'a')
insert into t values (1, 'b')
insert into t values (2, 'c')
select (select * from t t1 where t1.id = t2.id for xml raw('t'), type)
from t t2
group by t2.id
HTH
Michael
"ACROWN" <ACROWN@.discussions.microsoft.com> wrote in message
news:6D633481-D083-4A18-9FB5-37AA9258FEEE@.microsoft.com...
> When I do something like "select * from customer for XML auto" I get a
> single
> string containing the xml for all customers in the customer table. I want
> one
> xml string (i.e., document) per row so I can process one document at a
> time.
> Is this possible?

How to return a row counter?

Is it possible to return a row counter in a single select statement?
For example, I want to return Col1 and Col2 in TableA, but I need a counter
to identify the returned row's positioning.
RowCount ColA ColB
1 A B
2 C D
3 E F
I'd like to do it in a single select statement. If I have to declare a
variable and do a while statement, I could do it but it would be very
process-intensive.
Thanks!> Is it possible to return a row counter in a single select statement?
Sort of. But you really, really, really should consider appending this at
the presentation layer, which has to loop through all rows anyway. Doing
this at the database level forces SQL Server to inspect the entire set for
every row, and this will definitely have high potential to cause you some
performance issues.
http://www.aspfaq.com/2427|||Thanks, Aaron. I'll look into those options.
Do you know if Microsoft is planning on implementing this logic as a
function in the future?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sort of. But you really, really, really should consider appending this at
> the presentation layer, which has to loop through all rows anyway. Doing
> this at the database level forces SQL Server to inspect the entire set for
> every row, and this will definitely have high potential to cause you some
> performance issues.
> http://www.aspfaq.com/2427
>
>|||> Do you know if Microsoft is planning on implementing this logic as a
> function in the future?
Yes, SQL Server added ROW_NUMBER() and other ranking functions in SQL Server
2005.
http://msdn2.microsoft.com/en-us/library/ms186734

How to return a range of rows in sql server

Hello,

John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me an
idea how to achieve paging in sql server without row number
functionality. Thank you John. The following works for me, not very
eficient though:

SELECT * FROM
( SELECT top 5 * FROM
( SELECT top 10 * FROM
( SELECT top 10 *
FROM dft_document
ORDER BY documentkey ASC
) a
ORDER BY documentkey DESC
) b
) d
ORDER BY documentKey ASC

The innermost SELECT gives 10 rows out of which last 5 needed.

regardsSeveral alternative methods described here:

www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--|||"Alex Ravvin" <alexravv@.hotmail.com> wrote in message
news:f497c8a4.0401291050.175bcef0@.posting.google.c om...
> Hello,
> John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me an
> idea how to achieve paging in sql server without row number
> functionality. Thank you John. The following works for me, not very
> eficient though:
> SELECT * FROM
> ( SELECT top 5 * FROM
> ( SELECT top 10 * FROM
> ( SELECT top 10 *
> FROM dft_document
> ORDER BY documentkey ASC
> ) a
> ORDER BY documentkey DESC
> ) b
> ) d
> ORDER BY documentKey ASC
> The innermost SELECT gives 10 rows out of which last 5 needed.
> regards

You can simplify this to

SELECT *
FROM (SELECT TOP 5 *
FROM (SELECT TOP 10 *
FROM dft_document
ORDER BY documentkey ASC) AS T10
ORDER BY documentkey DESC) AS T5
ORDER BY documentkey ASC

Regards,
jag

How to retrieve rows with no Duplicate in this case?

I need to get rows without dupicate ProductID. In the below query, the
subquery returns 1 row, which is fine. But the query itself returns 2 rows
because the TransactionDate has two same date. I want the quesry to return
only one row if it has the same ProductID.
TransactionID is the primary key, which determines the
uniqueness of rows. Qty is not important in the query, but we only need to
have it in the select. We query based on ProductID,StoreID and the date. We
want to get each row based on ProductID, and StoreID which have the
MaX(TransactionDate). Each row returned must have only one ProductID and
StoreID.
For example,
TransactionID ProductID StoreID QTY Date
1 Prd1 2 4
1/2/2005 9:32AM
2 Prd1 2 8
1/2/2005 9:32AM
3 Prd5 2 9
3/2/2005 9:33AM
4 Prd5 4 9
4/2/2005 9:33AM
If we want to get all the products which are in StoreID 2 within the date
that fall below 3/2/2005 10:00AM it must returne the following rows:
TransactionID ProductID StoreID QTY Date
2 Prd1 2 8
1/2/2005 9:32AM
3 Prd5 2 9
3/2/2005 9:33AM
Why? Because we want the last entered record for the ProductID in the
StoreID Within the date. If we see the subquery, this is right which return
s
the right record, but the main query returnes the other record as well, that
is TransactionID #1.
But when we join it with the main query we get wrong results, that is we get
TransactionID #1,2,3. We only want TransactionID #2, #3.
Note:
We cannot use Top 1 in the subquery, because we want to query for many
products which only returns one record for each ProductID. For example, if
there are ProductID 1,2,3,and 4 it must check for all the products if the
query does not specify the ProductID as a limit. It may even want to get all
products from all
stores within specified date. But it must only get 1 Same ProductID for ever
y
StoreID as the previouse example says.
The subquery below works fine, but I think the problem is in the outer query
.
Here is the query:
SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
FROM [Inventory Transactions] IT
LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
INNER JOIN (
SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
10:02:26'
AND StoreID IN(2) GROUP BY
Products.ProductID,StoreID) AS X
ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
IT.TransactionDate=X.MaxDate
ORDER BY Products.ProductName
MikeDon't have time for a full explanation, but try adding
HAVING TransactionDate = MAX(TransactionDate)
after the GROUP BY clause in your subquery. This should force it to return
only the row for the most recent sale.
"Mike9900" wrote:

> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to retur
n
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which retu
rns
> the right record, but the main query returnes the other record as well, th
at
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we g
et
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example, i
f
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get a
ll
> products from all
> stores within specified date. But it must only get 1 Same ProductID for ev
ery
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer que
ry.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Forget my earlier post. Could you post DDL for all tables involved in the
query, and sample data?
"Mike9900" wrote:

> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to retur
n
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which retu
rns
> the right record, but the main query returnes the other record as well, th
at
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we g
et
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example, i
f
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get a
ll
> products from all
> stores within specified date. But it must only get 1 Same ProductID for ev
ery
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer que
ry.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Hi
Your problem is that the MaxDate is not unique for each ProductID AND
StoreID combination in [Inventory Transactions] (the accuracy of a datetime
value is one three-hundredth of a second). If TransactionID is guaranteed to
be ascending over time then you could use this instead of the date.
e.g (untested)
SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
FROM [Inventory Transactions] IT
LEFT JOIN Products P ON P.ProductID=IT.ProductID
JOIN (
SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
FROM [Inventory Transactions] T
LEFT JOIN Products Q ON Q.ProductID=T.ProductID
WHERE T.TransactionDate<='03/02/2005 10:02:26'
AND StoreID IN(2)
GROUP BY Q.ProductID,T.StoreID) AS X
ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
IT.TransactionID=X.MaxTID
ORDER BY P.ProductName
You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on how
to post DDL and example data that can be usable.
John
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to
> return
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
> We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which
> returns
> the right record, but the main query returnes the other record as well,
> that
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we
> get
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example,
> if
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get
> all
> products from all
> stores within specified date. But it must only get 1 Same ProductID for
> every
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer
> query.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Thanks for the answer.
TransactionID is the primary key that is incremented by the system. It is
gurantedd I think, don't you?
--
Mike
"John Bell" wrote:

> Hi
> Your problem is that the MaxDate is not unique for each ProductID AND
> StoreID combination in [Inventory Transactions] (the accuracy of a datetim
e
> value is one three-hundredth of a second). If TransactionID is guaranteed
to
> be ascending over time then you could use this instead of the date.
> e.g (untested)
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> LEFT JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionID=X.MaxTID
> ORDER BY P.ProductName
> You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on h
ow
> to post DDL and example data that can be usable.
> John
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
>
>|||The other tables are Store with StoreID as primary key, Products with
ProductID as the primary key. These two keys are references in this table.
--
Mike
"Mark Williams" wrote:
> Forget my earlier post. Could you post DDL for all tables involved in the
> query, and sample data?
> "Mike9900" wrote:
>|||Thanks for the help.
We want it based on TransactionDate not the TransactionID. There could have
been a transaction that had lower date but the TransactionID is the highest.
This occurs, for example, when the user wants to post a transaction for
previous month today. If the user post the previouse month transaction today
,
TransactionID is Max but the TransactionDate is not Max.
We want a combination of both Max(TransactionDate) and Max(TransactionID) to
be true, but here we see that the TransactionDate could not be unique and
TransactionID is not correct by itself.
--
Mike
"John Bell" wrote:

> Hi
> Your problem is that the MaxDate is not unique for each ProductID AND
> StoreID combination in [Inventory Transactions] (the accuracy of a datetim
e
> value is one three-hundredth of a second). If TransactionID is guaranteed
to
> be ascending over time then you could use this instead of the date.
> e.g (untested)
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> LEFT JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionID=X.MaxTID
> ORDER BY P.ProductName
> You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on h
ow
> to post DDL and example data that can be usable.
> John
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
>
>|||Hi
This tends to imply that you transaction date is a confusing name! If these
can be back dates then you will potentially get different results each time
you run the query and it will not be historically static. If you don't have
sufficient auditing of the changes to your data it may be hard to prove why
you get different results.
Try something like:
SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
FROM [Inventory Transactions] IT
JOIN Products P ON P.ProductID=IT.ProductID
JOIN (
SELECT X.ProductID, X.StoreID, MAX(I.TransactionID) AS MAXTID
FROM [Inventory Transactions] I
JOIN (
SELECT Q.ProductID, T.StoreID, MAX(T.TransactionDate) AS MaxDate
FROM [Inventory Transactions] T
LEFT JOIN Products Q ON Q.ProductID=T.ProductID
WHERE T.TransactionDate<='03/02/2005 10:02:26'
AND StoreID IN(2)
GROUP BY Q.ProductID,T.StoreID) AS X ON I.ProductID=X.ProductID AND
I.StoreID=X.StoreID AND I.TransactionDate=X.MaxDate
) Y ON IT.ProductID=Y.ProductID AND IT.StoreID=Y.StoreID AND
IT.TransactionID=Y.MAXTID
ORDER BY P.ProductName
John
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:4149F671-75A3-4A82-95BD-841CF2FC55C4@.microsoft.com...
> Thanks for the help.
> We want it based on TransactionDate not the TransactionID. There could
> have
> been a transaction that had lower date but the TransactionID is the
> highest.
> This occurs, for example, when the user wants to post a transaction for
> previous month today. If the user post the previouse month transaction
> today,
> TransactionID is Max but the TransactionDate is not Max.
> We want a combination of both Max(TransactionDate) and Max(TransactionID)
> to
> be true, but here we see that the TransactionDate could not be unique and
> TransactionID is not correct by itself.
> --
> Mike
>
> "John Bell" wrote:
>|||Thank you John, it is working.
--
Mike
"John Bell" wrote:

> Hi
> This tends to imply that you transaction date is a confusing name! If thes
e
> can be back dates then you will potentially get different results each tim
e
> you run the query and it will not be historically static. If you don't hav
e
> sufficient auditing of the changes to your data it may be hard to prove wh
y
> you get different results.
> Try something like:
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT X.ProductID, X.StoreID, MAX(I.TransactionID) AS MAXTID
> FROM [Inventory Transactions] I
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionDate) AS MaxDate
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X ON I.ProductID=X.ProductID AND
> I.StoreID=X.StoreID AND I.TransactionDate=X.MaxDate
> ) Y ON IT.ProductID=Y.ProductID AND IT.StoreID=Y.StoreID AND
> IT.TransactionID=Y.MAXTID
> ORDER BY P.ProductName
> John
>
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:4149F671-75A3-4A82-95BD-841CF2FC55C4@.microsoft.com...
>
>|||Create a view that captures the max date
Create view VwMaxDate as
select ProductID, Max(Date) as MaxDate
group by ProductID
Then, you can join your table to the view and get the results you are
sing.
Select a.TransactionID, a.ProductID, a.StoreID, a.QTY, a.Date
from TableA a join VwMaxDate on a.ProductId = b.ProductId and a.Date =
b.MaxDate

How to Retrieve only nth row as a result by writing one query.

How to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:

SNo SudentName Marks
001 Ashok kumar 67
002 Anderson 70
003 Alfred 60
004 Ameeruddin 65
005 KalyanKumar. 69

Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.

How to write this query in SQL Server.

Query part 1: Select the top three records and ordered by marks (ASC)

Query part 2: select all records where the marks value is the MAX value of the first query part

Do you need to take into consideration, any tied value (e.g. two at 71) - how does this affect those with a score of 70?

|||

SELECT TOP 1 *
FROM (SELECT TOP 3 * FROM MyTable ORDER BY Marks DESC) t1
ORDER BY Marks ASC

|||A nice answer, if I may say|||It's a start, but he still has to deal with what to do with duplicates as you previously mentioned.

how to retrieve a row by specifying row number

Hi,
Is there any sql command to retrieve a particular row by specifying its
row number."supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
Oh I can't wait for the replies to this one :-)

>|||hi
you can use this type of Query
This Query is from Pubs DB
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2
WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname
Hope this helps you
Regards
Shrinivas|||Hi
What vesrion are you using?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
--1 (ROW_NUMBER())
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC
--2
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker
--3
DECLARE @.pagenum AS INT, @.pagesize AS INT
SET @.pagenum = 2
SET @.pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@.pagenum-1)*@.pagesize+1 AND @.pagenum*@.pagesize
ORDER BY score DESC, speaker
"supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
>