Query problem in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have table named : PODetail
Fields are : POID, PONO, PODate, ItemCode, ItemName, Rate.

I want Last 3 PONo by Item.

for example,

SQL

Item  | PONO | Rate
------------------
Item1 | PO50 | 1000
Item1 | PO49 | 1001
Item1 | PO48 | 1002
Item2 | PO25 | 2001
Item2 | PO24 | 2002
Item3 | PO23 | 2003

I get OutPut Like this which I have try, but it is not giving only first 3 by Item. It show all PO. I cant solve this..please help me.

What I have tried:

SQL

Select row_number() over(PARTITION BY ItemCode order by ItemCode) srno, 
tbl_ItemMaster.ItemCode, PONO, Rate
From PODetail
Order By ItemCode, PODate DESC

Posted 4-Apr-20 3:49am

Member 9720862

Updated 4-Apr-20 5:15am

Maciej Los

v2

Add a Solution

Comments

[no name] 4-Apr-20 10:00am

Have you thought about something like SELECT TOP 3....?

Member 9720862 4-Apr-20 10:52am

No. I Want OutPut which I have written in "for Example...". I want this Output..

[no name] 4-Apr-20 10:55am

.

Member 9720862 4-Apr-20 10:53am

I want Item Wise Last 3 PONO...which i have wrinten in "For Example.."

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-04-04T05:17:00

If you would like to get 3 records of every item:

SQL

DECLARE @PODetail TABLE(ItemCode VARCHAR(30), PONO VARCHAR(30), Rate INT, PODate Date)

INSERT INTO @PODetail(ItemCode, PONO, PODate, Rate)
VALUES('Item1', 'PO50', '2019-12-27', 1000),
('Item1', 'PO49', '2019-12-28', 1001),
('Item1', 'PO48', '2019-12-29', 1002),
('Item2', 'PO25', '2019-12-27', 2001),
('Item2', 'PO24', '2019-12-28', 2002),
('Item2', 'PO23', '2019-12-29', 2003),
('Item1', 'PO39', '2019-12-30', 1000),
('Item1', 'PO38', '2019-12-31', 1001),
('Item1', 'PO37', '2020-01-02', 1002),
('Item2', 'PO36', '2019-12-30', 2001),
('Item2', 'PO35', '2020-01-02', 2002),
('Item3', 'PO34', '2020-01-02', 2003)

SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY PODate DESC) srno, ItemCode, PONO, Rate, PODate
    FROM @PODetail
) T
WHERE srno IN (1, 2, 3)

OriginalGriff · Answer 2 · 2020-04-04T04:20:00

Solution 1

Try:

SQL

SELECT TOP 3 ItemName as Item, PONO, Rate FROM MyTable ORDER BY ItemCode, PODate DESC

Posted 4-Apr-20 4:20am

OriginalGriff