|
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...)
|
|
|
|
|
_Damian S_ wrote: National Association of Dyslexics
No. It is a pledge that I will Never Disrespect Alcohol.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
I have a table with these fields:
Season Day DateStart DateEnd WeekNumber MonthNumber MonthName Year
S Sunday 2/1/2009 2/1/2009 1 1 February 2009
F Saturday 1/31/2009 1/31/2009 53 12 January 2009
.
.
.
This is a daily fiscal calendar. I need to get the season, Day the season started, Date of the season start, Date of the season end, week number of season start, month name of season start, and the year of season start. Because this is daily calendar I am thinking I can get the MIN(DateStart), and WeekNumber grouped by WeekNumber and year.
Season Date_Start Week_Number Month_Number Month Year
S 2001-01-28 1 1 February 2001
S 2002-01-27 1 1 February 2002
S 2003-01-26 1 1 February 2003
S 2004-02-01 1 1 February 2004
S 2005-01-30 1 1 February 2005
S 2006-01-29 1 1 February 2006
S 2007-01-28 1 1 February 2007
S 2008-01-27 1 1 February 2008
S 2009-02-01 1 1 February 2009
Now I know the start date. I can also get the End date of each season by getting the MAX(DateEnd), and WeekNumber grouped by WeekNumber and Year.
S 2001-07-28 00:00:00 26 6 July 2001
S 2002-07-27 00:00:00 26 6 July 2002
S 2003-07-26 00:00:00 26 6 July 2003
F 2004-07-31 00:00:00 26 6 July 2004
S 2005-07-30 00:00:00 26 6 July 2005
S 2006-07-29 00:00:00 26 6 July 2006
S 2007-07-28 00:00:00 26 6 July 2007
S 2008-07-26 00:00:00 26 6 July 2008
S 2009-08-01 00:00:00 26 6 July 2009
Now I know both start dates of each season, and end dates of each season. For example, in 2001 Spring season starts on first line of first table, and ends on first line of second table. How can I put it together into one table? Basically I need to match them by row number but I don't know how to do so in SQL 2000? This is the final result set structure I need but the data is not correct:
Season Date_Start Date_End Week_Num Month_Num Month_Name Year
S 2001-01-28 00:00:00 2001-07-28 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2002-07-27 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2003-07-26 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2004-07-31 00:00:00 1 1 February 2001
CodingYoshi
Artificial Intelligence is no match for Human Stupidity.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
