Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
I don't understand this. I want to do relatıon wıth two tables, but I want to get the last row in the second table.
 
I write:
select customer.name,Last(person.number) as X from customer,person where customer.id=person.id
 
What's wrong here? The LAST is not workıng.
Posted 1-Jun-10 3:37am
Edited 1-Jun-10 10:01am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Adjusting William's answer, I would suggest something of the sort:
 
DECLARE @intLastNumber int
 
SELECT @intLastNumber = MAX(person.number) FROM person
 
SELECT
  [C].[name],
  [P].[number]
FROM
  customer [C]
  INNER JOIN person [P]
    ON [C].[id] = [P].[id]
WHERE
  [P].[number] = @intLastNumber
 
If you have to perform the operation in one query, just perform another join like so:
 
SELECT
  [C].[name],
  [P].[number]
FROM
  customer [C]
  INNER JOIN person [P]
    ON [C].[id] = [P].[id]
  INNER JOIN
  (
    SELECT MAX(person.number) AS [LastNumber] FROM person
  ) [SQ]
    ON [P].[number] = [SQ].[LastNumber]
 
Something like the above would work as well. I haven't executed any of these, but I believe they should work.
  Permalink  
Comments
William Winner at 1-Jun-10 16:46pm
   
That definitely is a more efficient answer.
 
I was just trying to keep it simple since they clearly don't understand SQL.
Andrew Rissing at 1-Jun-10 18:02pm
   
No problem. I understand where you were coming from.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I would suggest studying SQL a little more and trying to understand each part of the SELECT statment in more detail because you clearly don't understand what it happening.
 
I'll try to break down the SELECT for you quickly.
This is the basic structure
SELECT [columns] FROM [tables] WHERE [whereclause]
 
So, [columns] is telling the query what information to put into each row. [tables] is telling the query where to get the data. [whereclause] is telling the query what data to get the information from.
 
So, here's what the SELECT statement is actually doing. You're saying, that each row will have two columns, customer.name and Last(person.number). This means that for any of the data it returns, each row will show the customer name and the last value in the person.number column. That will go in every row returned.
 
Then, you tell it to get the data from customer and person and return a row for each time that the id's match.
 
What you need to do is specify that you only want to return the one row, not every row.
 
Henry's suggestion won't work because you can't have aggregate functions in the where clause but if could be modified to something like:
SELECT customer.name, person.number
FROM customer, person
WHERE (customer.id=person.id AND
       person.number=(SELECT Last(person.number) FROM person))
  Permalink  
Comments
Andrew Rissing at 1-Jun-10 16:19pm
   
While the above query may be correct, it will execute the query for each row in the table, which would lead to bad performance.
Henry Minute at 1-Jun-10 16:43pm
   
Thaks for your comment. Good to know.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hows about something like:
SELECT customer.name, person.number as X
 FROM customer, person
 WHERE customer.id = person.id AND
   person.id = Max(person.id)
 
Please note: this is off the top of my head, may not work.
  Permalink  
Comments
William Winner at 1-Jun-10 13:49pm
   
Reason for my vote of 2
close...but you can't have aggregate functions in the where clause.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

How about the following:
 
select TOP 1 customer.name, person.number
from customer,person
where customer.id=person.id
ORDER BY customer.name DESC, person.number DESC
 
This will invert the order of the table (last customer.name and last person.number for that customer), and only return 1 row.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

I wrote an article that should help you with this: TOP 1 JOIN
 
I cannot tell exactly what you want though, so there might be an easier way.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

select top(1) * from table_name order by id desc
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 379
1 Nirav Prabtani 266
2 Abhinav S 210
3 PIEBALDconsult 160
4 Dave Kreskowiak 155
0 OriginalGriff 7,545
1 Sergey Alexandrovich Kryukov 6,757
2 Maciej Los 3,909
3 Peter Leow 3,693
4 CHill60 2,712


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 2 Jun 2010
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100