Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dood
Starting Member
3 Posts |
 Posted - 2001-12-11 : 12:43:51
|
| I've posted this question to the sqlmag form and another, Okay here goes.I have a 2 tables called a = Suppliers [contains supplier details, SupId (int, Pk), Username, Fname, etc] b = ItemType [ItemId (int, Pk), SupId (fk), ItemName, etc, modifieddate (datetime, getdate())]Relation bewteen the 2 is = 1 [supplier] to Many [ItemTypes]I would like to retrieve the LAST ROW ENTERED BY ALL USERS in the ITEMTYPE table. I'll clarrify, this bit has caused a bit of confusion. NOT THE LAST ROW ENTERED by ANY USER, but the last row entered by e.g USER 1, USER 2, USER 25. Users 1, 2, 25 could have entered 50 rows. Once again it is the last ROW entered by the users. I have used the MAX(modifieddate) function, THIS produces the last row entered by the last user. Any help or pointers would be GREAT. big thanks in advance dood |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-11 : 12:53:37
|
| If you had an identity column, wouldn't @@identity do this for you. It would return the last row inserted by a SPID and you could then just query for that row.Mike "A program is a device used to convert data into error messages." |
 |
|
|
dood
Starting Member
3 Posts |
Posted - 2001-12-11 : 13:26:13
|
| like your quotes, the second one rocks.Back to SQL, could you explain that for me about the @@identity (i'm newish to sql) the setup/situation is as follows. i am trying to get these results into a view so then i can then query these results (view) from a web page. The web page is going to request the specific row based on the field "SupId" ...chears for the help..have a nice day dood |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-11 : 13:34:58
|
| Oh, ok, if you're trying to get these into a query-able view, I don't think @@identity would work for you then. Basically, it returns the last identity value inserted by your SPID. For a return value from an insert statement, it would work fine, but not if you want to store the last record in a view. Based on that, would it be possible to tag the inserts with the userid of who did the insert and then query based on the max(modifieddate) grouped by user? I think that's probably the easiest approach to take.Mike "A program is a device used to convert data into error messages." |
|
|
|
TonyH
Starting Member
29 Posts |
Posted - 2001-12-11 : 17:03:30
|
| Hi,I may be barking up the wrong tree here, but do you mean the last row entered in the ItemType table by each username in the associated Suppliers table row? And if so, I assume that a Username may be associated with >1 SupId? If so, try the following:select s.username, max(i.modifieddate)from suppliers s inner join itemtype i on s.supid = i.supidwhere i.modifieddate = (select max(i2.modifieddate) from itemtype i2 where i2.supid = i.supid)group by s.username TonyHwww.SQLCoder.com - Code generation for SQL Server 7/2000 |
|
|
|
dood
Starting Member
3 Posts |
Posted - 2001-12-12 : 07:55:01
|
| Tony, keep barking it works a treat. sincere gratitude Dood |
|
|
|
|
|
|
|
|
Last row inserted by users (how do i ) Help!!!