T-SQL (SS2K8) :: Select MAX Per Group?

Jul 22, 2015

I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.

I am able to get one MAX row per Group but not all the Rows which has MAX value.

Here is my Table sample.

CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT

[code]....

In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.

View 7 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

T-SQL (SS2K8) :: Group By Performance FK / PK

Jul 10, 2014

We have a medium sized database with the next tables:

- PA: 525000 records
- PR: 780000 records
- R: 1000 records
- B: 45 records

PA: PK = PAARDCODE

PR: PK = PAARDREGISTERCODE
PR: FK = PAARDCODE
PR: FK = REGISTERCODE

R: PK = REGISTERCODE
R: FK = BOEKCODE

B: PK = BOEKCODE

When I group by B.BOEKCODE the query lasts: 10 (or more when 'where' option is added) seconds
When I group by R.BOEKCODE the query lasts less than 2 seconds.

SELECT B.BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
FROM PA
INNER JOIN PR ON PA.PAARDCODE = PR.PAARDCODE
INNER JOIN R ON R.REGISTERCODE = PR.REGISTERCODE
INNER JOIN B ON R.BOEKCODE = B.BOEKCODE
GROUP BY BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?

View 9 Replies View Related

T-SQL (SS2K8) :: Group By With Nulls?

Jun 12, 2015

Table Clients is master table with all records, joining against Assets table that may or may not have a matching entry.

Trying to sum an asset type against table and no matter what kind of join I do I cannot get SQL to return a NULL match against the Clients ID value. All I get back are matching rows.

Here's the SQL:

select c.cindex,SUM(a.value) AS 'Total Assets' from Clients c
"the join" Assets2012 a on a.clientid=c.cindex
where (c.ClientClass<=7 AND c.ClientClass<>6) AND a.assettype = 2
group by c.cindex

But no matter what type of join I do, left, right, left outer, inner, I am not getting back NULL values for client records that have no matching asset records.

Strange thing is, by removing the "and assettype=2" part I get the whole database back, with NULL's but not the range I'm looking for.

View 2 Replies View Related

T-SQL (SS2K8) :: Group Columns To One Row In A Table

Apr 1, 2014

I have a table with the following columns

Num,ID,Pos,Value
74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo

I am trying to get the result as follows.

Num Id Final Value

74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo

I tried to use Stuff and XMLpath but it is not giving me distinct results.

View 4 Replies View Related

T-SQL (SS2K8) :: MIN Across Multiple Columns With GROUP BY

Jan 13, 2015

How to get the lowest U.Price along with FX and Rate.

ID-U.Price-FX-Rate

1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00

Required output.

ID-U.Price-FX-Rate

1280 6.32 RM 1.00

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

T-SQL (SS2K8) :: Skipping Group With FOR XML Path

May 18, 2015

I have a table with a text field and a grouping number.

1, A
1, B
1, C
1, D
1, E

2, A
2, C
2, D
2, E

3, A
3, B
3, C
3, F

4, A
4, B
4, D
4, F
4, G

I assemble the text fields into strings by group, using the FOR XML PATH('') function, so that I end up with

1, A-B-C-D-E
2, A-C-D-E
3, A-B-C-F
4, A-B-D-F-G

This all works fine. I now have a requirement to leave out some groups, based on whether the GROUP does or does not contain a certain text. For instance, if I want only GROUPS with 'E', my result set should be

1, A-B-C-D-E
2, A-C-D-E

If I want only those GROUPS that DO NOT have 'E', my result set should be

3, A-B-C-F
4, A-B-D-F-G

I can (and have) put a condition on the assembled string, but it seems to me that it should be possible to make the cut earlier. That is, rather than assembling the string, scanning it with a Like '%E%' condition and discarding what doesn't meet my needs, I would like to stop scanning the incoming GROUP as soon as a mismatch is detected.

If I want all groups that will not contain an 'A', for instance, it is pointless for the query engine to read in 'A', 'B', 'C', 'D', 'E', put them all together into a string, compare it with a wildcard and throw it away. As soon as it sees the 'A', it has enough information to completely skip over the rest of that group. The abbreviated examples here are trivial, but the real code is not.

All I've been able to dream up so far results in skipping the RECORD that contains the 'A', but not the entire group.

View 9 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:

TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445

OUTPUT

ProductIDSeqNoBalance
111510
121445

View 3 Replies View Related

T-SQL (SS2K8) :: Update Records Separated By Group

Jun 25, 2014

I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:

Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;

I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.

InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450

I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".

CREATE TABLE STATEMENT:

CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,

[Code] ......

View 3 Replies View Related

T-SQL (SS2K8) :: Group By And Order By - Location Starting With A And B

Jul 7, 2014

I am having below schema.

CREATE TABLE #Turnover (
location varchar(50),
Total int
)

insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)

Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.

View 5 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

T-SQL (SS2K8) :: Group Sum LEN On VARCHAR Column And Concatenate

Jul 3, 2015

I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?

View 1 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt 

View 2 Replies View Related

T-SQL (SS2K8) :: How To GROUP BY With Shortest Distance By Account Number

Mar 11, 2014

Given the following example;

declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('1','46','8.4325654'),('2','44','7.8754345'),('2','45','1.54654323'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),
('3','46','17.4325654'),('4','44','7.8754345'),('4','45','1.54654323'),
('4','46','11.5436543')

How can I show the shortest Distance by AccountID and StoreID. Results would look like this;

AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300

View 7 Replies View Related

T-SQL (SS2K8) :: Checking User Active Directory Group

Jul 17, 2014

Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?

View 6 Replies View Related

T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:

SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]

[Code] .....

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT

SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)

It will return the correct Total amount but not the right numbers for the Attempt groupings...

View 1 Replies View Related

T-SQL (SS2K8) :: How To Group Total Count Of Similar Items

Mar 30, 2015

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.

So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
--------------------------
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

View 6 Replies View Related

T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?

Mar 30, 2015

In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table

[Code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Table Valued Function For Active Directory Group Membership

Jun 8, 2012

For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?

The following works fine:

SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL

The following gives me the error:

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE

[Code] .....

View 7 Replies View Related

T-SQL (SS2K8) :: Sequential Data Selection - Identify Different Fields Within A Group Of Records?

Jun 18, 2014

How to identify different fields with in a group of records?

Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'

[Code] ...

I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.

View 1 Replies View Related

T-SQL (SS2K8) :: Select XML Node With Namespaces

Feb 15, 2012

I am trying to select data from a column that has xml data in it. I have tried all I can think of, but the results keep coming back as NULL. Not sure why but I pasted the top of the xml document so show what I am trying to do.

- <ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
- <ST>
<ST01_TransactionSetIdentifierCode>837</ST01_TransactionSetIdentifierCode>
<ST02_TransactionSetControlNumber>1001</ST02_TransactionSetControlNumber>
<ST03_ImplementationGuideVersionName>005010X222A1</ST03_ImplementationGuideVersionName>
</ST>

And the query...

WITH XMLNAMESPACES('[ns0]' AS ns)
SELECT sourceclaimxml.value('(/ST01_TransactionSetIdentifierCode)[2]', 'varchar(300)') XMLValue
FROM xclaim_audit_xml
CROSS APPLY sourceclaimxml.nodes('//ST') as P(nref)
WHERE edixid='2323111'

View 9 Replies View Related

T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?

May 6, 2014

I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL

[code]....

So this would be the resultset:

1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27

View 3 Replies View Related

T-SQL (SS2K8) :: Select If Null Passed Else By INT

May 14, 2014

I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.

ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Making Two Select Statements Into One

Oct 13, 2014

I have a scenario where the second SELECT statement in my query has to be removed such that the logic needs to be included in the CTE select statement itself.

How this can be done? The columns in the final SELECT statement is having bit complexity.

My query is below:

WITH Local_EOL_Dioxin_Statistic_Import_New
AS
(
SELECT
CASE
WHEN ISNUMERIC(vr.stringValue) = 1 THEN vr.stringValue

[Code] .........

View 6 Replies View Related

T-SQL (SS2K8) :: How To Select Columns That Have Some Values Only

Jun 1, 2015

I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.

Here is an example:

IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),

[Code] .....

In this case, I'd like to run a query like this one(skip Column Event3):

Select eventId,Category,events1,events2,events4,events5 From #events

View 4 Replies View Related

SELECT Using GROUP BY - Please Help Me, Please

Jan 5, 2008

hello,
i have two tables: Pictures and UserComments, both have PictureID column
in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date
i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1
FROM Pictures INNER JOIN
UserComments ON Pictures.PictureID = UserComments.PictureID
WHERE (UserComments.UserName = @UserName)
GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1
 if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated
but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs
How can i order the results by Date Desc?
sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples
please help me, thanks

View 4 Replies View Related

Select Top, Group By

Feb 7, 2008

 Hello!
This is my query:SELECT tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted,
tblMessages.intMessGroup
FROM tblMessages INNER JOIN
tblUsersToGroups ON tblMessages.intMessGroup = tblUsersToGroups.belongsToGroup INNER JOIN
tblUsers ON tblUsersToGroups.userId = tblUsers.id
WHERE (tblUsers.userName = @strUserName)
GROUP BY tblMessages.intMessGroup, tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy,
tblMessages.datePosted
ORDER BY tblMessages.intMessGroupWhat I want to do is select the top 3 from each intMessGroup. In other cases where I'd want to Group By a computed column I could have used Rank, but since the column intMessGroup contains static values (message group id's) there should be some easy peasy way to do this, or?Thanks beforehand for any and all replies!Cheers!/Eskil

View 7 Replies View Related

How To Select The Top 1 Row In Each Set Of Group????

Mar 27, 2008

 hai guys...am new to sql....plz help me out........ the below is my table structure.....Table Name : #temp pgm_main_category_id         pgm_sub_category_id                filepath                                                                                             17                                                       15                  photo/Writer/Content/Sports/Basketball/bb1.jpg                                                       17                                                       16                  photo/Writer/Content/Sports/Cricket/cricket1.jpg                                                     17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg                                                       18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg                                                       18                                                       19                  photo/Writer/Content/Nature/Tree/tree1.jpg                                                           18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg                                                       19                                                       21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg                                                       19                                                       22                  photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg                                                 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg                                                     17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg     from this table i need the query output as below......for example: This is my expected output pgm_main_category_id         pgm_sub_category_id               
filepath                                                                                            

17                                                      
15                 
photo/Writer/Content/Sports/Basketball/bb1.jpg
18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg    19                                                      
21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg  17                                                      
16                 
photo/Writer/Content/Sports/Cricket/cricket1.jpg      18                                                      
19                 
photo/Writer/Content/Nature/Tree/tree1.jpg       19                                                      
22                 
photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg    17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg 18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg              17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg      if anybody have an idea about this query plz send me...... i need this immediately guys.....thanks in advance......regards janu         

View 16 Replies View Related

Select, Group By, Sum...

Mar 29, 2006

Hello,I have two tables:Student (containing columns: name, id, status, amount)Uplate (containing columns: id, student_id as foreign key, date, payement)Students make payment from time to time and that payment goes to Uplate.How to list (What is SELECT statement) to getevery studentits idstatusamounttotal payment made till today! (like sum all payement for this student)Thanks in advance.

View 4 Replies View Related

How To Select The First Row For Each Group?

Oct 10, 2004

Hello, everyone:

There is a table as below that has three groups regarding to cus. How to select first cid for each group. Thanks.

zyt

cus cid
ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011
ANATR10308
ANATR10625
ANATR10759
ANATR10926
ANTON10365
ANTON10507
ANTON10535
ANTON10573
ANTON10677
ANTON10682
ANTON10856
AROUT10355
AROUT10383
AROUT10453
AROUT10558
AROUT10707
AROUT10741
AROUT10743
AROUT10768
AROUT10793
AROUT10864
AROUT10920
AROUT10953
AROUT11016

View 2 Replies View Related

Select Top 3 By Group

Feb 21, 2007

I need to find the first 3 days that a product is scheduled in one of my
tables. It may be scheduled on 12 different days, but I only want the first 3.

So, based on some sample data and this thread:

http://www.dbforums.com/t1115304.html


I was able to come up with a query that does what I need.
However, I have no idea how it works. For one, what
is Count(*) counting in the query?


create table #tmpSched (
sched_date datetime,
product char(3)
)

insert into #tmpsched
select '1/1/2001', 'abc'
union all
select '1/2/2001','abc'
union all
select '1/3/2001','abc'
union all
select '1/1/2001', 'def'
union all
select '1/2/2001','def'
union all
select '1/1/2001', 'ghi'
union all
select '1/1/2001', 'jkl'
union all
select '1/2/2001','jkl'
union all
select '1/3/2001','jkl'
union all
select '1/4/2001','jkl'

SELECT a.SCHED_DATE, a.PRODUCT
FROM #tmpSched a
INNER JOIN #tmpSched b
ON a.product = b.product
AND a.sched_date > = b.sched_date
GROUP BY a.product, a.sched_date
HAVING COUNT(*) <= 3
order by a.product, a.sched_date

drop table #tmpsched


Any explanation would be appreciated.

EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date

View 12 Replies View Related

Select TOP N Of Each Group

Sep 8, 2013

I'm trying to find the most effective way to get the top N of each group in a group byI've already try the ROW_NUMBER() OVER PARTITION Method but it is still slow ...I've also checked the CROSS APPLY Method not really more efficient..Bu t My group is very simple and limited to one column...So Now I'm on the way to use a stored procedure make a loop of TOP n for each distinct element of my column

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved