SQL For Smarties
-
create table TestLoan( loanid int identity(1,1), custid int, loanvalue varchar(50) ) GO INSERT INTO TestLoan values(2,'First Loan') INSERT INTO TestLoan values(2,'Second Loan') INSERT INTO TestLoan values(2,'Third Loan') INSERT INTO TestLoan values(2,'Fourth Loan') INSERT INTO TestLoan values(2,'Fifth Loan') GO Does anyone know a way I could return the 3 most recent loans in the same row? The trick would be making it always be the 3 most recent loans, no matter what else was added. *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
-
create table TestLoan( loanid int identity(1,1), custid int, loanvalue varchar(50) ) GO INSERT INTO TestLoan values(2,'First Loan') INSERT INTO TestLoan values(2,'Second Loan') INSERT INTO TestLoan values(2,'Third Loan') INSERT INTO TestLoan values(2,'Fourth Loan') INSERT INTO TestLoan values(2,'Fifth Loan') GO Does anyone know a way I could return the 3 most recent loans in the same row? The trick would be making it always be the 3 most recent loans, no matter what else was added. *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
Here is one that you might be able to use:
DECLARE @TotalLoanValue varchar(150) SELECT TOP 3 @TotalLoanValue = COALESCE(@TotalLoanValue + ', ', '') + loanvalue FROM TestLoan ORDER BY loanid DESC SELECT @TotalLoanValueThis will work with SQL 2K. I haven't tested it with any other DBs. It will return something like this: Fifth Loan, Fourth Loan, Third Loan I hope this helps :) Edbert P. Sydney, Australia. -
Here is one that you might be able to use:
DECLARE @TotalLoanValue varchar(150) SELECT TOP 3 @TotalLoanValue = COALESCE(@TotalLoanValue + ', ', '') + loanvalue FROM TestLoan ORDER BY loanid DESC SELECT @TotalLoanValueThis will work with SQL 2K. I haven't tested it with any other DBs. It will return something like this: Fifth Loan, Fourth Loan, Third Loan I hope this helps :) Edbert P. Sydney, Australia. -
Wow Edbert - I'm not even sure how this works. Isn't COALESCE meant to return the first non null value? Can you explain the mechanics a bit? *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
Sorry, it was 9AM in the morning and I haven't had my coffee ;P Yes, COALESCE is meant to return the first non-null value from the values passed. Have a look at this code:
SELECT TOP 3 @TotalLoanValue = COALESCE(@TotalLoanValue + ', ', '') + loanvalueThe first time the @TotalLoanValue variable is processed, it will return NULL as the value. I use COALESCE to return empty string ('') instead, so when I concatenate the next rows, they will not result in null. You can also useISNULL(@TotalLoanValue + ', ', '')instead ofCOALESCE(@TotalLoanValue + ', ', '')as they're basically doing the same thing, except that ISNULL only accepts two values. What the whole SQL statement does is just to concatenate the result of the query into one comma-delimited string. Just change the delimiter if you need to. Good luck! :-D PS: UseWHERE loanvalue IS NOT NULLif you want to always return 3 records. Edbert P. Sydney, Australia. -
create table TestLoan( loanid int identity(1,1), custid int, loanvalue varchar(50) ) GO INSERT INTO TestLoan values(2,'First Loan') INSERT INTO TestLoan values(2,'Second Loan') INSERT INTO TestLoan values(2,'Third Loan') INSERT INTO TestLoan values(2,'Fourth Loan') INSERT INTO TestLoan values(2,'Fifth Loan') GO Does anyone know a way I could return the 3 most recent loans in the same row? The trick would be making it always be the 3 most recent loans, no matter what else was added. *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
Self joins! There are a beautiful thing when some Neanderthal wants a denormalized report from you nice orderly normalized data structure.:wtf: Try this (works on sql server. Access is ???):
select top 1 * from testloan t1, testloan t2, testloan t3 where t1.loanid<>t2.loanid and t2.loanid<>t3.loanid and t1.loanid<>t3.loanid order by t1.loanid desc, t2.loanid desc, t3.loanid descBill