Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


SQL Help

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 13:51 - 20 Jul 2010    Post subject: SQL Help Reply with quote
i have 3 dates pulled from 3 tables (most recent in each)

i need to select the most recent from the 3,

any ideas on the best way to go about this?

at the moment i'm going down the

if D1 > D2 then MR=D1
if D1 > D3 then MR=D1
if D2 > D3 then MR=D2

but it feels wrong and sloppy to me

any ideas?
 Back to top
View user's profile Send private message You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 14:01 - 20 Jul 2010    Post subject: Reply with quote
Hi

I will guess at MySQL (cos it makes it easy Wink )

Code:
SELECT SomeDate
FROM
(
SELECT aDate As SomeDate
FROM Table1
UNION
SELECT bDate As SomeDate
FROM Table2
UNION
SELECT cDate As SomeDate
FROM Table3
)
ORDER BY SomeDate LIMIT 1


Or

Code:
SELECT MAX(SomeDate)
FROM
(
SELECT aDate As SomeDate
FROM Table1
UNION
SELECT bDate As SomeDate
FROM Table2
UNION
SELECT cDate As SomeDate
FROM Table3
)


All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Wabby
Trackday Trickster



Joined: 25 Sep 2007
Karma :
PostPosted: 14:42 - 20 Jul 2010    Post subject: Reply with quote
Code:
SELECT SomeDate
FROM
(
SELECT aDate As SomeDate
FROM Table1
UNION
SELECT bDate As SomeDate
FROM Table2
UNION
SELECT cDate As SomeDate
FROM Table3
)
ORDER BY SomeDate LIMIT 1


Will do it as KickStart has already said.
____________________
What restriction?
 Back to top
View user's profile Send private message You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 16:01 - 20 Jul 2010    Post subject: Reply with quote
beaten to it Smile
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 16:03 - 20 Jul 2010    Post subject: Reply with quote
annoyingly it don't like that

this is what i tried

Code:
1>  SELECT SOME_DATE FROM (
2> SELECT TOP 1 CREATE_DATE AS SOME_DATE FROM RESERVATION
3> WHERE BORROWER_ID=17087
4> UNION
5> SELECT TOP 1 CREATE_DATE AS SOME_DATE FROM ILL_REQUEST
6> WHERE BORROWER_ID=17087
7> UNION
8> SELECT TOP 1 CREATE_DATE AS SOME_DATE FROM LOAN
9> WHERE BORROWER_ID=17087
10> )
11> ORDER BY SOME_DATE LIMIT 1
12> GO


producing this error

Quote:
Msg 11753, Level 15, State 1:
Server 'SYBASE', Line 11:
The derived table expression is missing a correlation name. Check derived table
syntax in the Reference Manual.
1>


any ideas?
 Back to top
View user's profile Send private message You must be logged in to rate posts

TQ
Trackday Trickster



Joined: 17 Dec 2009
Karma :
PostPosted: 10:20 - 21 Jul 2010    Post subject: Reply with quote
Code:
1>SELECT TOP 1 RESERVATION.CREATE_DATE AS SOME_DATE FROM RESERVATION
2> WHERE RESERVATION.BORROWER_ID=17087
3> UNION
4> SELECT TOP 1 ILL_REQUEST.CREATE_DATE AS SOME_DATE FROM ILL_REQUEST
5> WHERE ILL_REQUEST.BORROWER_ID=17087
6> UNION
7> SELECT TOP 1 LOAN.CREATE_DATE AS SOME_DATE FROM LOAN
8> WHERE LOAN.BORROWER_ID=17087
9> ORDER BY SOME_DATE LIMIT 1
10> GO


I'm not familiar with Sybase nor do I have a server to test on but try that.
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 11:17 - 21 Jul 2010    Post subject: Reply with quote
not familar with sybase either myself but a 10 second google has told me that TOP doesnt work in sybase so you'll need to use ROWCOUNT instead.

as below..

Code:

SET ROWCOUNT 1
SELECT RESERVATION.CREATE_DATE AS SOME_DATE FROM RESERVATION
WHERE RESERVATION.BORROWER_ID=17087
UNION
ELECT ILL_REQUEST.CREATE_DATE AS SOME_DATE FROM ILL_REQUEST
WHERE ILL_REQUEST.BORROWER_ID=17087
UNION
SELECT LOAN.CREATE_DATE AS SOME_DATE FROM LOAN
WHERE LOAN.BORROWER_ID=17087
ORDER BY SOME_DATE DESC
SET ROWCOUNT 0


setting rowcount to 1 will only bring back 1 record

it should do this until rowcount is set back to 0 again

if it still doesnt work google the syntax for unions etc.. in sybase and make any changes you need to Wink

i presume there is only one record per borrower_id?
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 12:32 - 21 Jul 2010    Post subject: Reply with quote
Top works,

year the borrower id is unique.
 Back to top
View user's profile Send private message You must be logged in to rate posts

TQ
Trackday Trickster



Joined: 17 Dec 2009
Karma :
PostPosted: 14:51 - 21 Jul 2010    Post subject: Reply with quote
Did you try my code? Same error message?
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 15:07 - 21 Jul 2010    Post subject: Reply with quote
TQ wrote:
Did you try my code? Same error message?


LIMIT 1 doesn't work.

remove that and I get the following

Quote:
10> GO
SOME_DATE
--------------------------
Aug 25 2005 11:52AM
Jun 15 2004 9:33AM
Nov 10 2003 9:44AM

(3 rows affected)


however these are the oldest entries not the newest.
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 15:09 - 21 Jul 2010    Post subject: Reply with quote
just to check, i've ran the following

Quote:
1> SELECT TOP 1 CREATE_DATE FROM LOAN
2> WHERE BORROWER_ID=17087
3> ORDER BY CREATE_DATE DESC
4> go
CREATE_DATE
--------------------------
Jun 4 2010 2:04PM


same borrower, same table (loan in the case) and I get the top result.

i'll try that ROWCOUNT Option
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 15:17 - 21 Jul 2010    Post subject: Reply with quote
supZ wrote:
not familar with sybase either myself but a 10 second google has told me that TOP doesnt work in sybase so you'll need to use ROWCOUNT instead.

as below..

Code:

SET ROWCOUNT 1
SELECT RESERVATION.CREATE_DATE AS SOME_DATE FROM RESERVATION
WHERE RESERVATION.BORROWER_ID=17087
UNION
ELECT ILL_REQUEST.CREATE_DATE AS SOME_DATE FROM ILL_REQUEST
WHERE ILL_REQUEST.BORROWER_ID=17087
UNION
SELECT LOAN.CREATE_DATE AS SOME_DATE FROM LOAN
WHERE LOAN.BORROWER_ID=17087
ORDER BY SOME_DATE DESC
SET ROWCOUNT 0


setting rowcount to 1 will only bring back 1 record

it should do this until rowcount is set back to 0 again

if it still doesnt work google the syntax for unions etc.. in sybase and make any changes you need to Wink

i presume there is only one record per borrower_id?



Think we have a winner

Code:
1> SET ROWCOUNT 1
2> SELECT RESERVATION.CREATE_DATE AS SOME_DATE FROM RESERVATION
3> WHERE RESERVATION.BORROWER_ID=17087
4> UNION
5> SELECT ILL_REQUEST.CREATE_DATE AS SOME_DATE FROM ILL_REQUEST
6> WHERE ILL_REQUEST.BORROWER_ID=17087
7> UNION
8> SELECT LOAN.CREATE_DATE AS SOME_DATE FROM LOAN
9> WHERE LOAN.BORROWER_ID=17087
10> ORDER BY SOME_DATE DESC
11> SET ROWCOUNT 0
12> GO
 SOME_DATE
 --------------------------
        Jun  4 2010  2:04PM

(1 row affected)
1>


will give a proper go in the perl script now
 Back to top
View user's profile Send private message You must be logged in to rate posts

TQ
Trackday Trickster



Joined: 17 Dec 2009
Karma :
PostPosted: 15:20 - 21 Jul 2010    Post subject: Reply with quote
Yeah I missed the DESC from my Order By I think.

Hadn't heard of the SET ROW COUNT thing though.
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 15:33 - 21 Jul 2010    Post subject: Reply with quote
supZ's code has worked.

I printed some results earlier today (first 20 pages of 65).

manually went through the 1st 2 and my original code had a number of errors.

none of these are present in supZ's solution.


just need to check that renews and returns still add a line to loan etc and other dates that may able to give me a newer date (i.e. loan of ILL etc though I think that would be in the loan table)

thanks to all for the help.

rep will be added, not sure how much I can give out, so i'll start with supZ.
 Back to top
View user's profile Send private message You must be logged in to rate posts

TQ
Trackday Trickster



Joined: 17 Dec 2009
Karma :
PostPosted: 15:47 - 21 Jul 2010    Post subject: Reply with quote
Just realised you said you're doing this in a perl script. I know it's lazy but personally I would have returned the three dates (using my code) and compared them in perl.

At least you've got it working now.
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 16:12 - 21 Jul 2010    Post subject: Reply with quote
that's how i was trying to do it, the comparing bit was what threw me lol

maybe i should have mentioned perl earlier
 Back to top
View user's profile Send private message You must be logged in to rate posts

TQ
Trackday Trickster



Joined: 17 Dec 2009
Karma :
PostPosted: 16:28 - 21 Jul 2010    Post subject: Reply with quote
Comparing dates can be complicated. In some languages you can use a simple > but it depends on whether the value returned from the database is a date type or just a string.

Doing it in SQL is the correct method so if it's working I'd stick with it.
____________________
First Bike :Gilera DNA 50 | Second: Imported 1989 NSR125
Third: 1998 Yamaha XJ600N | Currently: 1999 Yamaha FZS600
 Back to top
View user's profile Send private message You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 16:34 - 21 Jul 2010    Post subject: Reply with quote
yeah, i kind got it working, until i released July was beating August due to the alphabet.

changed the dates to 00/00/0000 format and it was fine, but then it was a case of choosing the newest which my brain was failing on.

SQL option is working and I do agree it's the best option.
 Back to top
View user's profile Send private message You must be logged in to rate posts

supZ
World Chat Champion



Joined: 03 Feb 2009
Karma :
PostPosted: 16:42 - 21 Jul 2010    Post subject: Reply with quote
t121anf wrote:
supZ's code has worked.

I printed some results earlier today (first 20 pages of 65).

manually went through the 1st 2 and my original code had a number of errors.

none of these are present in supZ's solution.


heh glad i could help m8y Smile
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike
 Back to top
View user's profile Send private message You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 20:58 - 21 Jul 2010    Post subject: Reply with quote
t121anf wrote:
annoyingly it don't like that

this is what i tried


Probably needs an alias name for the subselect.

I would try and do as much as possible in SQL. Do you need this for each borrower id?

Also for dates you really need them in ccyymmdd format

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

t121anf
World Chat Champion



Joined: 23 Feb 2007
Karma :
PostPosted: 15:56 - 22 Jul 2010    Post subject: Reply with quote
yeah for every borrower, its working now Smile
 Back to top
View user's profile Send private message You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 16:09 - 22 Jul 2010    Post subject: Reply with quote
Hi

To save doing it separately for each borrower id then something like this might do it

Code:

SELECT SomeBorrowerId, MAX(SomeDate)
FROM
(
SELECT RESERVATION.BORROWER_ID AS SomeBorrowerId, MAX(RESERVATION.CREATE_DATE) AS SomeDate
FROM RESERVATION
GROUP BY RESERVATION.BORROWER_ID
UNION
SELECT ILL_REQUEST.BORROWER_ID AS SomeBorrowerId, MAX(ILL_REQUEST.CREATE_DATE) AS SomeDate
FROM ILL_REQUEST
GROUP BY ILL_REQUEST.BORROWER_ID
UNION
SELECT LOAN.BORROWER_ID AS SomeBorrowerId, MAX(LOAN.CREATE_DATE) AS SomeDate
FROM LOAN
GROUP BY LOAN.BORROWER_ID
) AS SubSelect1
GROUP BY SomeBorrowerId


You could probably lose the MAX and the GROUP BY from each of the unioned selects and just leave it round the outside. Might perform better or worse depending on your flavour of SQL.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts
Old Thread Alert!

The last post was made 16 years, 45 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.10 Sec - Server Load: 0.3 - MySQL Queries: 13 - Page Size: 119.92 Kb