How To Display Records Only Once Using Groupby In

Mar 31, 2008

in my sql query,when it run then it displays records as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

i want to display records only once like as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is

not contained in either an aggregate function or the GROUP BY clause.

so how can i design query so records display only once not repeated,or is any
alternative,to solve this .


my sql query as follows

SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as

dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,

dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,


op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type

,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,

m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')


uday

View 3 Replies


ADVERTISEMENT

Delete Records Not Matching Top25 In GroupBy

Jul 23, 2005

I have a table (tblA) that records the RecordID, UserID andLastViewedDate (DateTime) of each record opened in tblB where RecordIDis the PK in tblB. I want to construct a query that groups all recordsin tblA by RecordID, filters by UserID and keeps only the most recent25 RecordIDs and deletes the rest.This gets me a recordset of all RecordIDs filtered by UserID in tblAbut I can't figure out how to sort it by LastViewedDate DESC and toeliminate those not in the Top25:SELECT RecordIDFROM dbo.tblAWHERE (UserID = 1234)GROUP BY RecordIDAny help is appreciated!lq

View 4 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

Display DB Records In Label?

Oct 16, 2006

A SQL Server 2005 stored procedure expects a parameter UserID depending upon which it retrieves the no. of records & OrderIDs corresponding to the UserID from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records where UserID=6. In other words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 for UserID=6) & the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored procedure:ALTER PROCEDURE dbo.OrderCount    @UserID intASDECLARE    @OrderCount intSET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @UserID)SELECT @OrderCount AS OrderCount, OrderIDFROMNETOrdersWHEREUserID = @UserIDIn a VB class file, I am invoking the stored procedure in a function named GetOrderCount which returns a SqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6 in a Label control. Unlike the DataBinding controls like DataList, DataGrid, Repeater controls, the Label control will not automatically loop through the recordset. So I tried to accomplish this using a For....Next loopDim i As IntegerDim sqlReader As SqlDataReaderiUserID = Request.Cookies("UserID").Value'ShopCart is the name of the class in the VB class fileboShopCart = New ShopCartsqlReader = boShopCart.GetOrderCount(iUserID)While (sqlReader.Read)    If (sqlReader.GetValue(0) > 1) Then        pnlLinks.Visible = True        For i = 1 To sqlReader.GetValue(0)            lblLinks.Text = sqlReader.GetValue(1)(i)        Next i    Else        pnlLinks.Visible = False    End IfEnd WhileBut this generates the following error:No default member found for type 'Integer'.pointing to the linelblLinks.Text = sqlReader.GetValue(1)(i)in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in a Label control?

View 8 Replies View Related

Display Grouped Records

Apr 11, 2008

I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID     StoryTitle       StoryAuthor      Rating     StoryID    Comments
1      About Me       goodyone            6          20           Great Story
2      About Me       goodyone            5          20           Love your work
3      Hello World    magicme             6          26           What a Story
4      Hello World    magicme             7          26           This Reminds me of...
I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>"
SelectCommand="SELECT * FROM [iaw.comments]">
</asp:SqlDataSource>
 

View 5 Replies View Related

Display Distinct Records?

Dec 7, 2011

TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar

I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'

File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:

Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

View 3 Replies View Related

Display Records For This Week

Apr 15, 2008

hi friends,

how to display the records for this week?

for today i used this query to dispaly the values

select count(*) from tbl_voting v,tbl_lkvotefor l where v.voteforid=l.voteforid and v.creationdatetime=CONVERT(VARCHAR(10), GETDATE(), 101)

View 4 Replies View Related

Display The Records For This Month

Apr 15, 2008

hi friends,

how to display the records for current month?

View 7 Replies View Related

Display Repeated Records

Mar 5, 2014

finding the solution for the below query?It displays repeated records.

select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from

[code]....

View 2 Replies View Related

How To Display Records In Ms Sql Using Pivot

Mar 13, 2008

hi i want to display records as per data in ms sql 2005
my data is as follows


Table name & fields are as follows :--

1) Ab_Corporate_Project

Fields are :

Ab_crp_id- int
Contract_no varchar
Project_no varchar
Field_order_no varchar
Ab_type varchar
Owner_id int

Records in Table Ab_Corporate_Project is as follows :

Ab_crp_id Contract_no Project_no Field_order_no Ab_type Owner_id

1 123 pr123 fd222 LS 2
2 323 pr323 fo122 GMS 3
3 243 pr243 fo233 EMC 1

2) Ab_Plant_Project

Fields are :

Ab_plant_id int
Service_order varchar
p_type varchar
Owner_id int

Records in Ab_Plant_Project is as follows

Ab_plant_id Service_order p_type Owner_id
1 so111 LS 1
2 so123 T&M 3
3 so110 EMC 2


3)Other_Project

Fields are

Other_proj_id int
Po varchar
Otype varchar
Owner_id int
Records in Other_Project is as follows

Other_proj_id Po Otype Owner_id
1 p123 LS 3
2 p22 EMC 3
3 p231 GMS 2

4)Owner_representative

Fields are
Owner_id int
Owner_names varchar

Records in Owner_representative are as follows

Owner_id Owner_names
<!--[if !supportLists]-->1 <!--[endif]-->john
<!--[if !supportLists]-->2 <!--[endif]-->amey
<!--[if !supportLists]-->3 <!--[endif]-->nickols

5)Employee_table

Fields are
emp_id int
Emp_name varchar

Records in Employee_table are as follows

Emp_id Emp_name
1 andrue
2 handy
3 sims
4 sandy

6)Daily_Time_Entry

Fields are
Dt_id int
Daily_type varchar
Dt_date datetime
Emp_id int
Project_type varchar
Project_id int
Time_st int
Time_ot int
Time_dt int
Records in Daily_Time_Entry are as follows

Dt_id Daily_type Dt_date Emp_id Project_type Project_id Time_st Time_ot
1 Elect 12/03/2008 1 AB Corporate 2 10 8
2 Mech 13/03/2008 2 AB Corporate 1 10 10
3 Mech 13/03/2008 1 AB Plant 2 5 6
4 Elect 13/03/2008 3 AB Plant 3 7 8
5 Mech 13/03/2008 2 Other Project 2 5 6
6 Elect 13/03/2008 4 Other Project 3 10 8

Where dt_date is in dd/mm/yyyy format
Also in project_type field -- AB Corporate is taken for Ab_Corporate_Project ,
AB Plant for Ab_Plant_Project, Other Project for Other_Project
This means if Project_type = AB Corporate then search into Ab_Corporate_Project
Else if Project_type = AB Plant then search into Ab_Plant_Project
Else if Project_type = Other Project then search into Other_Project

Now I want to display records as per datewise suppose I want to view records by date 13/03/2008

Record display as follows

Dt_id,Dt_date,emp_id,Emp_name, Project_type ,project_id, contract_no, service_order,ab_type,p_type,otype,owner_id,owner_name,time_st,time_ot

So how can I write query to display records as per datewise display
Plz send me a query for this solution

View 1 Replies View Related

Display TODAY's Records...

Sep 1, 2006

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.



I tried this code but didn't work..


[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

View 12 Replies View Related

Display Count Of Records From The Same Table &> 1

Dec 27, 2007

How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State   UserName     First          Last         ID         City         CountTX       Kkeaton         Kathryn     Keaton     1001     Dallas      2TX       KakiKeaton    Kathryn     Keaton     1001     Dallas      2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig

View 4 Replies View Related

Query To Display Most Active Records

Feb 11, 2005

Hello,

I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

For example:

PromoID StartDate EndDate
------- --------- -------
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

Here's a query I am using to display all active promotions:

select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()

Thanks for your help.

D

View 4 Replies View Related

How To Display Unmatched Records In Two Tables

Feb 28, 2008

I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.

SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.

Only field3 is populated everything else is null.

select field3 from tbl1 group by field2 - record count = 48



SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2

-------------------------------------

Result Record count = 0

------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48

I used the left join and it didn't work.


I would like to build a query that will display all 3000 records.


Any help will be appreciated.

thank you!

View 4 Replies View Related

How Not To Display Number Of Records In SQLCMD

Jan 26, 2008

I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution. How do I do that?

Thanks in advance.

View 1 Replies View Related

Analysis :: MDX Display All Dimension Records

Jun 15, 2015

If I have a dimension with 3 records like this:

BrokerDimId  BrokerNm
1  Broker1
2  Broker2
3  Broker3

And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:

BrokerDimId  Amt
1  $100
2  $200

I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this

BrokerDimId  Amt
1  $100
2  $200
3  $0

Is this possible using MDX?

View 2 Replies View Related

Transact SQL :: Display Records According To Date

Oct 2, 2015

I have a table that have student names and their birth dates just like below

a) tblStudentInfo

which shows records like below (Birth Dates are shown in Year-Month-Day format)

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     DEF                        2002-09-10  
3     GHI                        2002-09-19 
4     JKL                        2002-06-10 

[code]...

I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     VWX                      2002-01-01
3     STU                        2002-02-03 
4     PQR                     2002-03-05 

[code]...

View 3 Replies View Related

Display Number Of Records Processed

Aug 20, 2007

I've got a stored procedure that processes a TON of records...

What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.

I have a simple counter defined in the procedure:

SET @COUNTER = 0

Each time the procedure loops through, it increments by 1:

SET @COUNTER = @COUNTER + 1

What I would like to do is write rows to a PROCESS table which would reads:

PROCESSED 1000 rows
PROCESSED 2000 rows
PROCESSED ...... rows
etc.

I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.

If anyone has any insight it would be greatly appreciated!!

Thanks in advance!

View 3 Replies View Related

How To Display Other Records From TopN Report

Oct 2, 2007



I was wondering if there was a way to display the "other" total that does not appear in a TopN report.

I.e. I display the Top 10 records

I would then like a row saying "All Others" and the subtotal of the non Top 10 records

I would also like to display a Total of the TopN records and what % of the overall that amounts to

Simple example.....

Top 3 Report

1. AAA 10
2. BBB 9
3. CCC 8
4. Others 20

Top N Total 27 (% of total = 57%)
Overall Total 47


Lines in italics are the values I can't seem to get my head around

View 5 Replies View Related

Query To Display Records In Given Range?

May 11, 2015

I have a column in the table which contains below values

Range
XXXX100
xxxx101
xxxx102
...
xxxx1020

below values are strings. I tried using below query but it does not return all values in the range.

Select * from table where Range Between 'XXXX100' AND 'xxxx1020'

May be in need to extract the number to display the values in given range.

View 4 Replies View Related

T-SQL (SS2K8) :: Find Records In A String And Display

Jul 6, 2015

I am having a column which is ntext and contains below type of data.

{ "running":"true", "all":{ }, "GPAs" : [ {"type":"item", "alias":"i_11111"} ,"GPA":"1.75" }, {"element": {"type":"item", "alias":"i_11111"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_33333"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_44444"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_22222"} ,"GPA":"1.42" }, {"element": {"type":"item", "alias":"i_55555"} ,"GPA":"15" }, {"element": {"type":"item", "alias":"i_66666"} ,"GPA":"10" } ]}

above is a single row. There are many such rows are present in the table. Now I want to find all alias and GPA and display as below.

idGPA

111111.75
111111.43
333331.43
444441.43

In oracle it can be done by using REGEXP_SUBSTR. But how can we do this in SQL?

View 1 Replies View Related

Alternate Week Database Records Display

Oct 4, 2013

In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query.

View 17 Replies View Related

MDX Query To Display No Records For Empty Rows

Jan 9, 2007

Hi,

I am facing with the following problem.

I am using bar chart to display my report.

My MDX query is as follows:

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

where <criteria>

I want to show the topmost 10 records. For some criteria I get the results in the chart.

But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.

In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.

If I remove the TopCount clause then I get the above message, which is obvious.

So how do I acheive the same message but at the same time limiting the records to 10?

How can I acheive this in my scenario? Can something be done at the query end?

any help is appreciated.

Thanks in advance!

View 3 Replies View Related

How To Display Records Across The Page In Reporting Services?

Apr 14, 2008



I have created a table and want to display a datset. One of the fields in the dataset is 'month' which I need displayed across the top of the report. The other information should make up the rows.

How do I get it to list the Month across the page rather than down?

View 7 Replies View Related

How To Display All Records In One Page (disable Pagination)

Dec 22, 2006

Hi,

I use an embedded ReportViewer control as a LocalReport in a web form. The report displays the results in four pages and I need to display them all in one page (without any pagination). Is there a way to disable pagination? Also, what do I need to do to show the print button in toolbar?

Thanks,
Adarsh

View 3 Replies View Related

Enterprise Manager Cannot Display Table Records Or Run Query

Jul 23, 2005

I have a new installation of SQL Server 2000 Dev Edition on a Win2K3 Standard Edition Server that I used for development. I just set this machine up in th last week and installed all Win2K3 patches and then installed SQL2K and SP3a. I have a single named instance. I just noticed today that I cannot view table data or use the Query part of EM. When I right click a table and select Open Table->Return All Rows it gives me an error dialog "An unexpected error happened during this operation". While the EM is diplaying this dialog the EM screen looks like Internet Explorer and says "Action Canceled - You might not have permission to view this directory or page using the credentials you supplied." I believe that this is a EM issue as I cannot view table content on other remote server. ANy ideas? Might this be an IE security patch disallowing some connectivity ?--Frank--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

How Display Number Records In Query Designer VisualStudio.NET

Jul 23, 2005

I'm using the query desinger in ASP.NET , however the number of recordsin the resultset are not displaying, so I cut and paste it into Queryanalyzer which is silly.How do I set this in the output window, or result grid?Thanks Moe

View 1 Replies View Related

Max With Groupby

Apr 8, 2008

i have one query:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0)
order by salestitle


i m getting more than 1000 rows.. in output
some of them are :

salesid salestitle
---------------------------------
11871/20/2005 9:45:54 AM
821622K8_KTBcalendar_pre1 9/19/2007 10:46:02 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
175660AmerHeartMo_2008 2/22/2008 1:05:24 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
25186Cholesterol_Educ_Mo_2006_Rem_test
25282Cholesterol_Educ_Mo_2006_Rem_test


i want (desired)output like: -
11871/20/2005 9:45:54 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test

i want max(salesid) among that same salestitle

when i m doing like:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)

i m getting an error
Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

(0 row(s) affected)

can anybody help me to get correct output.

thanks.

View 9 Replies View Related

Use Of Groupby And Get Max Val

Feb 10, 2007

Respected Friends,
kindly solve my query.

Table Structrue:
Table Name : Custom_Table_SPPR_Master
Fields :
Sno int pk
SPPR_No int pk
Revison_No int pk
rev_Date date
prop_amt decimal
claim_amt decimal
avail_amt decimal
Status int

Data
Sno SPPR_No Revison_No rev_Date prop_amtclaim_amtavail_amtStatus
1 2000 0 2006-10-01 00:00:00.000 1000 600 400 2
2 2000 1 2006-10-01 00:00:00.000 1100 600 500 2
3 2000 2 2006-10-01 00:00:00.000 1400 600 800 2
4 2000 3 2006-10-01 00:00:00.000 1500 900 600 2
5 2001 0 2006-10-01 00:00:00.000 1000 200 800 2
6 2001 1 2006-10-01 00:00:00.000 1200 600 600 2
7 2002 0 2006-10-01 00:00:00.000 1000 600 400 2
8 2002 1 2006-10-01 00:00:00.000 1100 600 500 2
9 2003 0 2006-10-01 00:00:00.000 1000 600 400 2


I want the record where max of the revision no is seen for each sppr
that is the result should the rows of sno: 4,6,8,9


i tried the following but in vein

select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no
-- in this i am getting records, but i want * datas

select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no


select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no)
group by sppr_no,revision_no

Kindly let me the answers . thanks in advance.

View 2 Replies View Related

Using MAX And GROUPBY

Apr 23, 2007

Hi,

I need help in creating a query using MAX and group by.

The input SQL table ClientInfo contains data like the following:

Client Div MaxDt
00003 00 19820501
00003 00 19830208
00003 00 19850801
00003 00 20040811
00003 50 19850101
00003 50 20040811
00003 99 19870131

I need the output to have the max date for every division of every client and output should be as follows:

Client Div MaxDt
00003 00 20040811
00003 50 20040811
00003 99 19870131

Thanks,

View 7 Replies View Related

Groupby

Mar 17, 2008

Hi.I've wrote this query:

SELECT TOP 10 COUNT(*) AS ItemsSold, ProductName, order_details_product_id, SUM(order_details_price) AS order_details_price
FROM tbl_Detail_Order_Product
WHERE (OrderStatus NOT IN (14, 15))
GROUP BY order_details_product_id, ProductName, order_details_price
ORDER BY ItemsSold DESC

which works fine and have these results:
Purchased     Returned    Sold     Part Number    
$ 598.29                         37     16684    
$ 200.29                         33     16654    
$ 300.29                         25     16184    
.
.
.
my problem is I want to show the returned column wich is:

WHERE (OrderStatus IN (14, 15))
I dont know how can I do that.
I tried to write another query but it didnt worked correctly.
plz help me.
thanks

View 1 Replies View Related

SELECT Query Syntax To Display Only The Top Record For Duplicate Records

Oct 6, 2005

Good day!

I just can't figure out how I can display only the top record for the duplicate records in my table.

Example:

Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05

My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05

Thanks.

View 12 Replies View Related

Transact SQL :: Get Query By Selecting Month From Dropdown List And Display Records?

Oct 8, 2015

Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output

select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate

[Code] .....

View 5 Replies View Related







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