|
Hi Friends,
I need help in the following query.
SELECT A.BILL, A.BILLER, A.VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL
The above query is working fine. and o/p like below:
A3 3A 3
A3 3A 2
A3 3A 1
But now requirement is that, the table A consists VERSION_NO.
For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER
may have different VERSION_NO.
Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination.
I needs the o/p shd be
A3 3A 3
Likewise for all the rows. Pls help me.
Thanks in Advance.
Regards,
|
|
|
|
|
I don't know Oracle but in SQL Server I would use Top 1 and order by version_no desc. There will be something similar in Oracle.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: There will be something similar in Oracle.
Yes. Oracle uses rownum. Something like,
Select * from table where rownum = 1 order by version_no desc
|
|
|
|
|
Hi Navaneeth,
Thank you so much.
I understood the rownum=1 will work when i do order by version_no desc.
But in this case, order by is dynamic..that is user-given input. So whatever column user wish to come as order by he ll select that.
Can u help me how in that case i have to use this query.
Thanks lot really!!!
Regards,
|
|
|
|
|
Navaneeth,
Thanks.
If I user rownum=1 order by version_no desc, this will give the very first row, but i want the rows with version_no = max(version_no) in every code,unit_id combination. If I have 3 rows with this combination, among the 3 rows i wanted to display the row which is having max(version_no).
Kindly help me. Thanks.
Regards,
Sageetha
|
|
|
|
|
And just using MAX doesn't work?
SELECT A.BILL, A.BILLER, MAX(A.VERSION_NO) AS VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL
My advice is free, and you may get what you paid for.
|
|
|
|
|
I have device running Windows CE 5.0.
I am developing an application for it in C# using a SQL server mobile database.
In order to install the database on the device,I first have to add SQLserverCE.dll.But I don't know why the folder or dll is not available. I tried to install Microsoft SQL Server MObile Developer Tools.but still nothing.
I also reinstalled SQl Server 2005...
|
|
|
|
|
Hello friends,
I having two table, one table havin only one fields.
eg : Table A
release
rel1
rel2
rel3
I want to select datas from other table.
table B
release case ID
rel1 a 1
rel1 b 1
rel2 a 2
rel3 c 3
I want to select the cases and ID from table B where release is all the datas present in TAble A. I can build the query to get case and ID. Actually i need the query for another purpose.
I used below query
SELECT cases, ID FROM TAble B WHERE release IN(Select release from TAble B)
this query is very slow for my purpose
The query should link both the table
Please help me to opimise the query
Regards
Jishith
|
|
|
|
|
Try this
SELECT cases, ID
FROM TAbleB b
Join TableA a on a.release = b.release
and check your indices
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
i have a simple stored procedure and i want to split postcode in it which could be of length either 7 or 6 and tha second part will always be of length 3. am pasting the sql here. and also the error message. Plz help me resolve this error
<code>
SELECT CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,
if Postcode.length = 7
begin
set Postcode1= substring(Postcode,5,7)
set Postcode1= substring(Postcode,1,4)
end
else
begin
set Postcode1 = substring(Postcode,4,6)
set Postcode1 = substring(Postcode,1,3)
end
FROM xyz
WHERE (abc = @def)
END
</code>
<ERROR>
error:
Msg 102, Level 15, State 1, Procedure XYZ, LINE 7
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure XYZ, Line LINE 12)
Incorrect syntax near '='.
</ERROR>
|
|
|
|
|
What database are you using? The syntax for if Postcode.length = 7 doesn't look like it will work - certainly not in sql server. In sql server it would be
SELECT CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,
POSTCODE1 = case when len(postcode) = 7 then substring(Postcode,5,7) else substring(Postcode,4,6) end,
postcode2 = case when len(postcode) = 7 then substring(Postcode,1,4) else substring(Postcode,1,3)
FROM xyz
WHERE (abc = @def)
hope this helps
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanx Ashfield for ur reply I figured it out earlier itself and have implemented it almost the same way.
copying my code incase it helps anyone else.
<pre>
LEN(CONVERT(varchar, DecryptByKey(Postcode))) as Postcodeln,
case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '7' then
substring(CONVERT(varchar, DecryptByKey(Postcode)),1,4)
else
substring(CONVERT(varchar, DecryptByKey(Postcode)),1,3)
end Postcode1,
case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '6' then
substring(CONVERT(varchar, DecryptByKey(Postcode)),4,6)
else
substring(CONVERT(varchar, DecryptByKey(Postcode)),5,7)
end Postcode2,
</pre>
Many Thanks
|
|
|
|
|
No problem, glad to have helped
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Brand New & Updated for 2009 you can sell these turnkey websites and Php scripts. Individually, or as a complete package! YOU will keep 100% of every sale! This Package gives you all that and a whole lot more. Visit us for more detail. http://www.clicknearn.net/3016-64.html
|
|
|
|
|
Stop spamming and pay for your adverts for what is probably a crap product anyway
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi
I have a value in Datetime Datatype as '8/8/2009 7:45:00 AM'
i want to get only
7:45 AM
How to get this...........
|
|
|
|
|
A bit messy, but
select ltrim(right(convert(varchar,getdate(),0),7))
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
The first Community Technology Preview (CTP) of Microsoft SQL Server 2008 R2 is now available to you from TechNet.
There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.
Many other features. See SQL Server 2008 R2 CTP Open to All[^] for download and feature information.
|
|
|
|
|
brucedkyle wrote: Report Builder 3.0
But I still can't embed a SQL 2008 report in a VS 2008 winforms app b/c the report viewer does not support the new Tablix control - AAArrrgghhh. The only way to deploy a SQL 2008 report is via reporting sevices, what a PITA.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
brucedkyle wrote: There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.
[homer simpson voice]Oohhh...shiney objects in my reports...[/homer simpson voice]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi,
I m having three store procedures
1. Test_1
2. Test_2
3. Test_3
in Test_1 i get output of one date
in Test_2 i user Test_1 to get output of one week
in Test_3 i want output of 7 weeks so for that when i use Test_2 in Test_3 i got error
"Insert exec statement cannot be nested"
please help me out of this.
Thank you.
|
|
|
|
|
I looked in my crystal ball, stirred the tea leaves, and tried the medicine man lodge and I was still unable to see your query. Do you think your could be so kind as to post it here so we might be able to fathom better your problem. I can not express how happy that would make all of us.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Jerry Hammond wrote: Do you think your could be so kind as to post it here
Apparently not - I wonder if it is embarassment or he considers his code too proprietry to post, or maybe he worked it out himself.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, his lawyer is preparing an NDA document for you to sign. 
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Luc Pattyn wrote: NDA
The National Association of Dyslexics? rofl...
(I know what it really is btw...)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
