how to write query
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
Here is the bit that will get the highest sno for each user
SELECT MAX(sno) AS sno, name FROM MyTable GROUP BY nameAdd that as a subquery and join on to it like another table. Like this:SELECT s.sno, s.name, m.desc
FROM MyTable AS m
INNER JOIN (SELECT MAX(sno) AS sno, name FROM MyTable GROUP BY name) AS s
ON s.sno = m.sno AND s.name = m.name
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
for this you have to use corolated queries following the example SELECT sno, name, desc FROM WHERE sno=(SELECT MAX(sno) FROM
Thanks Warm Regards Prakash-B
-
for this you have to use corolated queries following the example SELECT sno, name, desc FROM WHERE sno=(SELECT MAX(sno) FROM
Thanks Warm Regards Prakash-B
PrakashBhaskar wrote:
for this you have to use corolated queries following the example SELECT sno, name, desc FROM <table name> WHERE sno=(SELECT MAX(sno) FROM <table mame) I think ur problem has solved
That will return the row "
8 suma jsakdksladksadks" only.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website
-
PrakashBhaskar wrote:
for this you have to use corolated queries following the example SELECT sno, name, desc FROM <table name> WHERE sno=(SELECT MAX(sno) FROM <table mame) I think ur problem has solved
That will return the row "
8 suma jsakdksladksadks" only.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website
i am sorry i have mis-understood the question
Thanks Warm Regards Prakash-B
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
You can use following query select max(no),name from trial1 group by name If have any questions, thn let me know
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
You can use following query select max(no),name from trial1 group by name If have any questions, thn let me know Dipti Jadhav
-
You can use following query select max(no),name from trial1 group by name If have any questions, thn let me know
d_d_jadhav wrote:
You can use following query select max(no),name from trial1 group by name If have any questions, thn let me know
No, that will result in:
8 suma
only. Which does not match the output the OP requested.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website
-
You can use following query select max(no),name from trial1 group by name If have any questions, thn let me know Dipti Jadhav
One post is enough.
Regards, Satips.:rose: Don't walk in front of me, I may not follow; Don't walk behind me, I may not lead; Walk beside me, and just be my friend. - Albert Camus
-
Hi I have created one table in sql server as sno name desc 5 suma dckdfjdskfldkf;dsf 3 suma lklkjdkjfkjfdjbbbbb 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl 2 manu klswkqewr 1 manu shdjsdksd like this.... now i want to dispaly one record fro each user (i.e the record which is having max sno for each user) i.e 8 suma jsakdksladksadks 4 manu nsdskdjsdsadl how to write query for this plz help me Thanks in advance
Haritha
The answer for ur question is here select sno,name,[desc] from trial1 where convert(varchar(50),sno)+name in(select convert(varchar(50),max(sno))+name from trial1 group by name) Please let me know, if u have any questions/comments.