i don't know how to make query.....

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

i have two table.
one is
section table
____________

id name class section
1 sankar VI A
2 pandian VI A
3 raja III C

second one is
paid_table
_____________

id sectionid paid_amount Balance
100 1 500 200
101 1 200 0
102 2 1000 500
103 2 200 300

i need is

name balance

101 0
103 300

-------------------------------------------
what should i do for generate query....

( i need last transaction of balance for VI class A section student. )

Posted 9-Dec-11 5:18am

uss_sankar

Add a Solution

Comments

Amir Mahfoozi 10-Dec-11 0:11am

Why don't you save pay date time ? you will get into trouble probably in the future.

5 solutions

Solution 1

Just off the top of my head, not tested

SQL

SELECT Name, Balance
FROM section
JOIN paid_table ON paid_table.sectionid = section.id
WHERE paid_table.id = (SELECT MAX(id) FROM paid_table WHERE sectionid = section.id)

Posted 9-Dec-11 5:29am

Not Active

Comments

Rubaba 11-Dec-11 6:23am

5+

Solution 2

You could also write it in this way:

SQL

SELECT Name, Balance FROM section, paid_table
WHERE paid_table.sectionid = section.id AND 
paid_table.id = (SELECT MAX(id) FROM paid_table WHERE sectionid = section.id)

Posted 9-Dec-11 6:46am

fgoldenstein

Solution 3

I love to use the RANK() function.
I have been successful in improving performance using it, especially if you have to deal with very large tables.

SQL

SELECT t.Name, t.Balance
FROM
	(
	SELECT	s.Name AS Name
		, p.Balance As Balance
		, RANK() OVER (PARTITION BY s.name ORDER BY p.id DESC) as rowNum
	FROM section s
	INNER JOIN paid_table p
	ON p.sectionid = s.id
	) t
Where t.rowNum=1

Posted 9-Dec-11 8:42am

FastEvo8

Comments

Monjurul Habib 10-Dec-11 15:17pm

review your code it will return 103 as first row.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Monjurul Habib · Accepted Answer · 2011-12-10T09:11:00

Solution 5

Try the following, and hope it will work;

SQL

SELECT     sec.name, paid.Balance
FROM         section sec INNER JOIN
                      paid_table paid ON paid.sectionid = sec.id
WHERE     (paid.id =
                          (SELECT     MAX(id)
                            FROM          paid_table
                            WHERE      (sectionid = sec.id)))

Posted 10-Dec-11 9:11am

Monjurul Habib

Updated 10-Dec-11 9:23am

v2

Comments

RaviRanjanKr 10-Dec-11 16:13pm

My 5!

Monjurul Habib 10-Dec-11 17:51pm

thank you

Rubaba 11-Dec-11 6:22am

5+

Monjurul Habib 11-Dec-11 7:43am

thank you rubaba

thatraja 11-Dec-11 7:21am

5!

Monjurul Habib 11-Dec-11 7:43am

thank you RAJA

uss_sankar 12-Dec-11 0:29am

Thank you for ALL Reply...

Monjurul Habib 12-Dec-11 0:41am

please mark answer as accepted if it helps

Monjurul Habib 12-Dec-11 0:53am

thanks

Eduard Lu · Accepted Answer · 2011-12-10T05:58:00

Solution 4

Try this one:

SQL

SELECT       name, balance
FROM         section
INNER JOIN   paid_table ON paid_table.sectionid = section.sectionid
WHERE        paid_table.id = (SELECT max(id) FROM paid_table 
                                             WHERE class = 'VI'
                                             AND section = 'A')

*remove max if you want to get 101 and 103 :) what i did here is what you said on your last sentence. :)
( i need last transaction of balance for VI class A section student. )

Regards,
Eduard

Posted 10-Dec-11 5:58am

Eduard Lu

Updated 11-Dec-11 14:32pm

v2

Comments

Monjurul Habib 10-Dec-11 15:14pm

your script is not ok. it will return only one.

[no name] 11-Dec-11 7:53am

because the OP said

( i need last transaction of balance for VI class A section student. )

and i am quite confused with his result query because it returned 2 rows. I just followed what he said from the highlighted part.

Monjurul Habib 11-Dec-11 8:16am

As sankar & pandian belongs to class=VI & section=A, it should return both.

[no name] 13-Dec-11 20:34pm

thank u for deducting 16 consecutive pts whoever u are! hater! :)