Select Statement in Ms. SQL Server 2000

How to make a select statement to get the last data from a table?

Page 1 of 1

3 Replies - 1536 Views - Last Post: 14 June 2009 - 10:15 AM Rate Topic: -----

#1 emresutisna  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 21
  • View blog
  • Posts: 80
  • Joined: 14-May 09

Select Statement in Ms. SQL Server 2000

Post icon  Posted 14 June 2009 - 08:15 AM

Hi,

I'm trying to make a select statement to get the last data from a table.
I can do it in Ms. Access 2007 with these codes :

SELECT PRODUCT_PRICE.PRODUCT_ID, LAST(PRODUCT_PRICE.PRICE_DATE),
LAST(PRODUCT_PRICE.PRICE) FROM PRODUCT_PRICE
GROUP BY PRODUCT_PRICE.PRODUCT_ID

But the above codes didn't work when I tried it in Ms.SQL Server 2000.
If there's somebody know how to solve this problem, please tell me ASAP...

Thanks for the help...

Regards,
Emre

Is This A Good Question/Topic? 0
  • +

Replies To: Select Statement in Ms. SQL Server 2000

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4422
  • View blog
  • Posts: 12,289
  • Joined: 18-April 07

Re: Select Statement in Ms. SQL Server 2000

Posted 14 June 2009 - 08:29 AM

You are going to build a record set and order it in descending order. The first record then is the last row of the result, just like the last function in Access.

SELECT PRODUCT_PRICE.PRODUCT_ID, PRODUCT_PRICE.PRICE_DATE,
PRODUCT_PRICE.PRICE FROM PRODUCT_PRICE
GROUP BY PRODUCT_PRICE.PRODUCT_ID
ORDER BY PRODUCT_PRICE.PRODUCT_ID DESC



Then you pull off the first row of data which is the last record of your result set.

:)
Was This Post Helpful? 0
  • +
  • -

#3 emresutisna  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 21
  • View blog
  • Posts: 80
  • Joined: 14-May 09

Re: Select Statement in Ms. SQL Server 2000

Posted 14 June 2009 - 09:14 AM

View PostMartyr2, on 14 Jun, 2009 - 07:29 AM, said:

You are going to build a record set and order it in descending order. The first record then is the last row of the result, just like the last function in Access.

SELECT PRODUCT_PRICE.PRODUCT_ID, PRODUCT_PRICE.PRICE_DATE,
PRODUCT_PRICE.PRICE FROM PRODUCT_PRICE
GROUP BY PRODUCT_PRICE.PRODUCT_ID
ORDER BY PRODUCT_PRICE.PRODUCT_ID DESC



Then you pull off the first row of data which is the last record of your result set.

:)


Thanks for the help but I still have problem. The above codes didn't work because the 'PRICE_DATE and PRICE'
aren't in aggregate function.
And if it's work, the result still show the record which has the same PRODUCT_ID I guess.
These are the examples of my records in PRODUCT_PRICE table :

PRODUCT_PRICE
--------------------------------------------
PRODUCT_ID | PRICE_DATE | PRICE
--------------------------------------------
0001 | 1-JAN-09 | $ 1.0
0001 | 2-JAN-09 | $ 1.5
0001 | 3-JAN-09 | $ 1.4
0001 | 4-JAN-09 | $ 1.3
0002 | 1-JAN-09 | $ 0.5
0002 | 2-JAN-09 | $ 0.6
0002 | 5-JAN-09 | $ 0.4
0002 | 6-JAN-09 | $ 0.7
--------------------------------------------

And the results I want just like these :
PRODUCT_PRICE
--------------------------------------------
PRODUCT_ID | PRICE_DATE | PRICE
--------------------------------------------
0001 | 4-JAN-09 | $ 1.3
0002 | 6-JAN-09 | $ 0.7
-------------------------------------------

Then could you help me and solve this problem?
Thanks for helping the beginner like me ... :)
Was This Post Helpful? 0
  • +
  • -

#4 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 44
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: Select Statement in Ms. SQL Server 2000

Posted 14 June 2009 - 10:15 AM

The LAST() function in Access is really interesting. It doesn't work very well, but everyone uses it. Providing this functionality in "real" SQL is not that easy.


To help you out, first I'll need to create a table like yours:

CREATE TABLE emresutisna
( PRODUCT_ID CHAR(4) NOT NULL,
PriceDate DATETIME NOT NULL,
PRICE Float NOT NULL)
GO



and then insert your sample data to it:

INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0001', '1-JAN-09', 1.0 )
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0001', '2-JAN-09', 1.5 )
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0001', '3-JAN-09', 1.4 )
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0001', '4-JAN-09', 1.3)
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0002', '1-JAN-09', 0.5)
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0002', '2-JAN-09', 0.6)
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0002', '5-JAN-09', 0.4)
INSERT INTO emresutisna (PRODUCT_ID, PriceDate, Price) VALUES ('0002', '6-JAN-09', 0.7)
GO



Then, I can write the query:

SELECT emresutisna.*
  FROM emresutisna
  JOIN
  (
	SELECT PRODUCT_ID, MAX(PRICEDATE) LastPriceDate
	  FROM emresutisna
	GROUP BY PRODUCT_ID
  ) AS LastRows ON LastRows.LastPriceDate = emresutisna.PriceDate AND LastRows.PRODUCT_ID = emresutisna.PRODUCT_ID




This uses a sub-select that produces the product_ID and price date where the last price date exists. That list doesn't include the price, though; it can't, since we don't have a set operation which will find the price for the matching "last" row. To find that, I join back to the data table and select all the columns from it where the key matches the last value I found going by price_date and grouping by the product_id.

It produces the results you say you're expecting:

PRODUCT_ID PriceDate			   PRICE
---------- ----------------------- ----------------------
0002	   2009-01-06 00:00:00.000 0.7
0001	   2009-01-04 00:00:00.000 1.3

(2 row(s) affected)



I hope that helps you.

This post has been edited by mikeblas: 14 June 2009 - 10:16 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1