Need to return only first row of one table in a group

Question

I have a sql Query Like this

Select a.column1,b.column2,c.column3 
from table1 a,table2 b, table3 c
where a.customerName = 'Michael' 
and a.id= b.id 
and a.id = c.id 
and b.id = c.id

My problem is table 1 and table 2 have only one row satisfying this condition always so there is no issues but table3 column 3 contains two rows so duplication fo rows happen just to display the two values of table3. How can I return only one row of c.column3 from table3 irrespective of how many rows it has?

I tried using

Select a.column1,b.column2,c.(TOP 1 column3) 
from table1 a,table2 b, table3 c
where a.customerName = 'Michael' 
and a.id= b.id 
and a.id = c.id 
and b.id = c.id

but still it did not work. Is there a way to achieve this? I am using SQL database.

Example :

select a.Full_Name FROM table1 a WHERE a.ID LIKE '%1002%'

this gives

Full_Name Arun TestName

But when I do

select a.FullName,b.work FROM table1 a, table2 b WHERE a.ID = b.id AND a.ID LIKE '%1002%'

This gives since work column for the ID 1002 has two rows (one row containing 2 and another row containing 1)

Full_Name work Arun 2 Arun 2 TestName 1 TestName 1

My expected output is instead of returning both the rows I would like it(work column) to return only one row. how is this achieved?

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Oct 08 '20 at 08:11
Tip of today: Always use modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. — jarlh, Oct 08 '20 at 08:14
Tip 2: Chose table aliases that make sense. a, b, c etc are poor table aliases. t1, t2 and t3 are much better (if your tables are named table1, table2 and table3.) — jarlh, Oct 08 '20 at 08:25

score 0 · Answer 1 · edited Oct 08 '20 at 08:21

0

You need to use the inner join one way or another to get only matching rows. This might help you to build the inner join: inner join

However, if you indeed get multiple correctly matched rows and want to get only the first one, use limit 1; at the end of your query.

edited Oct 08 '20 at 08:21

Dharman

30,962
25
85
135

answered Oct 08 '20 at 08:15

Eli Halych

545
7
25

Note that LIMIT is a product specific functionality, and OP is using TOP - specific for another dbms. – jarlh Oct 08 '20 at 08:17
Also, LIMIT is not the solution here. OP probably needs GROUP BY. – jarlh Oct 08 '20 at 08:18
Would be easier if there were exact names of tables and the context in the original question. – Eli Halych Oct 08 '20 at 08:22
I'd also like to see sample table data and the expected result. – jarlh Oct 08 '20 at 08:23

score 0 · Answer 2 · answered Oct 08 '20 at 08:17

if you really want any column3 of table3 then use GROUP BY and MAX

Select a.column1,
   b.column2,
   max(c.column3)
from table1 a,table2 b, table3 c
where a.customerName = 'Michael' 
and a.id= b.id 
and a.id = c.id 
and b.id = c.id
group by a.column1,b.column2

However, if there are more different values of c.column3 for each pair (a.column1, b.column2) then there is probably some rule which would select c.column3. My SQL simply selects the highest c.column3 value in such case.

score 0 · Answer 3 · answered Oct 08 '20 at 11:31

First, learn to use proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.

If you need lost of columns, then use row_number():

select a.column1,b.column2,c.column3 
from table1 a join
     table2 b
     on a.id = b.id join
     (select c.*, row_number() over (partition by c.id order by c.id) as seqnum
      from table3 c
     ) c
     on a.id = c.id and c.seqnum = 1;
where a.customerName = 'Michael'

Note that the condition b.id = c.id is redundant. The other two conditions imply that this is always true.

Need to return only first row of one table in a group

3 Answers3