please help quey reqiured - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

please help quey reqiured

Author

Topic

lovekhan12345
Starting Member

2 Posts

Posted - 2010-08-22 : 02:19:28

Hi
I have reqiured a query which extract data from the table which is based on
max date & time ,please help me

Tablename xyz
S.no Symbol Date &time Opening rate Current rate
1 hbl 21-08-2010 9:15:36 101 105
2 hbl 21-08-2010 9:15:40 101 106
3 hbl 21-08-2010 9:15:55 101 104
4 ppl 21-08-2010 9:15:36 125 130
5 ppl 21-08-2010 9:15:40 125 131
6 ppl 21-08-2010 9:15:50 125 128
7 ppl 21-08-2010 9:15:55 125 126

Out put

S.no symbol Date & time opening Currnt rate
3 hbl 21-08-2010 9:15:36 101 1104
7 hbl 21-08-2010 9:15:55 125 126

only query required for this output

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 07:20:04

try this:


select
y.*
from
(
select  
   max(S_no) as s_n
  ,Symbol
from XYZ
group by Symbol
) as x
join XYZ as y
on x.s_n = y.S_no
and x.Symbol = y.Symbol

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 07:22:54

you need to get the maximum serial_number (S_no) - if this one is unique (i hope), otherwise you'll need to define primary key or unique identifier in your table in order to get the right results.

lovekhan12345
Starting Member

2 Posts

Posted - 2010-08-22 : 09:08:20

Thank u but mistakenly i type two times hbl

but i want to on the basis of date and time

Hi
I have reqiured a query which extract data from the table which is based on max date & time ,please hel p me

Tablename xyz
S.no Symbol Date &time Opening rate Current rate
1 hbl 21-08-2010 9:15:36 101 105
2 hbl 21-08-2010 9:15:40 101 106
3 hbl 21-08-2010 9:15:55 101 104
4 ppl 21-08-2010 9:15:36 125 130
5 ppl 21-08-2010 9:15:40 125 131
6 ppl 21-08-2010 9:15:50 125 128
7 ppl 21-08-2010 9:15:55 125 126

Out put

S.no symbol Date & time opening Currnt rate
3 hbl 21-08-2010 9:15:36 101 1104
7 ppl 21-08-2010 9:15:55 125 126

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 10:14:59

so it's the same. try this:


select
y.*
from
(
select  
   max(date_time) as max_DT
  ,Symbol
from XYZ
group by Symbol
) as x
join XYZ as y
on x.max_DT = y.date_time
and x.Symbol = y.Symbol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:23:12

quote:
Originally posted by lovekhan12345

Thank u but mistakenly i type two times hbl

but i want to on the basis of date and time

Hi
I have reqiured a query which extract data from the table which is based on max date & time ,please hel p me

Tablename xyz
S.no Symbol Date &time Opening rate Current rate
1 hbl 21-08-2010 9:15:36 101 105
2 hbl 21-08-2010 9:15:40 101 106
3 hbl 21-08-2010 9:15:55 101 104
4 ppl 21-08-2010 9:15:36 125 130
5 ppl 21-08-2010 9:15:40 125 131
6 ppl 21-08-2010 9:15:50 125 128
7 ppl 21-08-2010 9:15:55 125 126

Out put

S.no symbol Date & time opening Currnt rate
3 hbl 21-08-2010 9:15:36 101 1104
7 ppl 21-08-2010 9:15:55 125 126

sorry didnt get that
how did you get output in blue?
datetime value is 21-08-2010 9:15:36 which is not maximum value for hbl. ALso didnt understand how you got 1104 as current charge. can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 13:00:12

visakh: i think he made two typo-mistakes. since serial numbers are the highest for each symbol.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 13:06:51