Using Group By And Order By With The Same Column

Apr 9, 2008

Hi All,

Please let me know if both query fetch the same result

Select Col1, Col2, Col3, Col4 from Table1 Group By Col1, Col2, Col3, Col4
Order By Col1, Col2



Select Col1, Col2, Col3, Col4 from Table1
Order By Col1, Col2

Thanks,
Muthu

View 7 Replies


ADVERTISEMENT

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:

Year
Month
Date

And the following row groups:

Region
Product
SubType (hidden, data at the date level is summed to Product)

At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

View 0 Replies View Related

Order Of GROUP BY - Does It Matter?

Oct 23, 2006

Does the order of columns make a difference in the GROUP BY?Woulde GROUP BY A, B, Creturn different results thanGROUP BY C,A,B ?

View 3 Replies View Related

Using Group By And Order By Clause

May 21, 2014

I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.

View 3 Replies View Related

Help Needed In Group By With Order By

Apr 15, 2008

hi,

my table data:

Sensor_Serial_No date_time Attribute_Flag
-----------------------------------------------------
2 2008-03-03 01:00:00 5
2 2008-03-03 06:00:00 5

my result should be like this

Sensor_Serial_No no of times occured Attribute_Flag lastoccurence

2 2 5
2008-03-03 06:00:00

please give me query for this please

View 2 Replies View Related

Group By And Order By Problem

Sep 22, 2007

I have the following statement that is working just fine:


SQL = "select* from products GROUP BY name ORDER BY price"

I have many products with the same name but with different prices and I want to display unique records along with other information but I want to display the cheapest product first. It is displaying the first record in the table with that name but not the cheapest distinct record in the list of all distinct product names.
Not sure of the above makes sense so this is the kind of data in the table.

Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30


When listing the products it will show the following order:

Test2, 20
Test1, 25
Test3, 30

However I want it to display:

Test1, 15
Test2, 25
Test3, 30

Thanks for any help.

Daniel

View 3 Replies View Related

Tricky Group By Order By Query

Jul 23, 2005

Hello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?

View 5 Replies View Related

Exactly-Once-On-Order - Conversation Group Question

Sep 30, 2007



Hi,

A SSB newbie question:

Looking in a various resources, i see that a conversation group guarantees receiving messages EOID, which means that each message will be received only once, and that messages will be received in the order they were sent.



However, the documentation explicitly states that only dialogs guarantee messages order. So, I€™m a little bit confused, what's the "In-Order" stand for in "Exactly Once In Order"?







Thanks,

Ido

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

Analysis :: Measure Group Execution Order

Sep 21, 2015

I am executing a CUBE which is having 9 measure groups, i need to know order of the execution of the measure groups.??

ex:
1. fsales
2.ftranssales
3.fitem
4.fstores..............etc...

I know, the dimension are executed first, but not aware of the order of execution of measure group.

View 4 Replies View Related

SQL Server 2014 :: Order By Month Number In A Group By

Dec 19, 2014

Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,

[code]...

How can I get the result orderd by the month number?

View 3 Replies View Related

Full-text Search With Order By Or Group By Is Unusable

Feb 13, 2008

Using SQL Server 2005 (9.0.3042), I can not get a full-text search query to work with order by or group by. For example, if I run the following query...


SELECT s.networkID, i.interID

FROM Interactions i

INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID

INNER JOIN Sessions s ON i.sessionID = s.sessionID

WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000


It returns two rows immediately.

If I add an "order by" or "group by" like...


SELECT s.networkID, i.interID

FROM Interactions i

INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID

INNER JOIN Sessions s ON i.sessionID = s.sessionID

WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000

ORDER BY s.networkID, i.interID


The CPU shoots to 100% and I have to kill the query. I have tried many variations of the above and I can not get the query to work with order by or group by. Is this a bug in SQL Server? Is there a fix or workaround?

View 1 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

Analysis :: Order Total For Orders Containing A Specific Product Group?

May 27, 2015

I have a request for being able to show ordertotals for those orders that contains a specific productgroup.

Case

Order 1   ProductGroup   Value
A 20
B 40
C 40
Total 100

Order 2   ProductGroup   Value
A 20
D 40
Total 60

So if i slice OrderTotal by ProductGroup the result will be

A 160
B 100
C 100
D 60
ALL            160

how to solve this.

View 9 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related

Order By 2 Column ?

Dec 14, 2003

i have i table tab1 and 2 columns date1<b> and <b>date2

i want order this cloumns but i have one thing

* if date1 < date2 then order by date2

* if date2 < date1 then order by date1

how i do it .?

thanx

View 3 Replies View Related

Column Order

Jun 6, 2001

Does anyone know how to change column order of a table with data.

View 6 Replies View Related

How To Order A Column

Nov 30, 2004

To order a recordset I can simply do
"Select * From Table Order by ColumnName;"

But my need is different.

In a column I have the following content:
"C"
"P"
"F"
"A"

But "F" and "A" may exist more than once.

I must order the recordset always in the order
"C"
"P"
"F"
"A"

and not "A", "C", "F", "P".

Can anyone pls help me?

Thanks in advance.

View 5 Replies View Related

Does Column Order Really Matter?

Sep 11, 2006

Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with?  I'm kind of confused.  

View 2 Replies View Related

Order By Column Alias

Feb 12, 2007

I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS
2 (
3 SELECT
4 Id,
5 Name,
6 Created,
7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers,
8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
9 FROM Projects p
10 )
11 SELECT *
12 FROM ProjectList
13 WHERE RowNumber BETWEEN 50 AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:
Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'.
I read this in the documentation:
When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

View 4 Replies View Related

Variable ORDER BY Column

Jan 20, 2000

I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.

View 1 Replies View Related

ORDER BY Any Column Problem

Jan 22, 2001

I am using SQL2000.

I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.

Is there a way to do this ?

I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope.
I've tried SETting a variable to the above @OrderByThisColumn ...nope.

Any ideas/clues would be a great help

The code...........

CREATE PROCEDURE usp_ShareHolders_Main_sel

@CompanyCode varchar(4),
@OrderByThisColumn varchar(30)

AS

SELECT
H.Fund_Man as Holders,
H.Shares as SharesHeld,
H.Share_Pric * H.Shares as Value,
H.Pcent as SharesOutstanding,
H.Shares - H.Shares as ShareChange,
C.Reg_Date as ReportDate,
'Register' as Source,
((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent,
H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight,
(H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit

FROM
Citywatch_Company C
Inner Join
Citywatch_Holders H
On
C.Epic = H.Epic
Inner Join
Citywatch_Sector S
On
H.Sector = S.Sec_Code
WHERE
C.Epic = @CompanyCode

ORDER BY <one of the select columns above>


Help me please someone
Paul

View 3 Replies View Related

ORDER BY - Join Column

Apr 6, 2007

Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.

Code:

select
id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
FROM se_links INNER JOIN
se_index AS se_index_1 ON
se_links.target_index_id = se_index_1.id AND
se_links.anchor LIKE :query))

View 1 Replies View Related

How To Order In Month Column

Nov 15, 2007

hi,

table ab contain two column
no dispaymonth
1 April
2 jan
3 Mar

when i using order by in Month column it will return Alpha/- order(April,Mar,jun like that) but i need Jan,Feb,March order

need that Query

Advance thanks

View 3 Replies View Related

Using The Same Column In Bother SUM And ORDER BY? How?

Jun 21, 2007

Hello,Using SQL 2005. Columns:ID, int (PK, auto-increment by 1)WorkHours, intName, varchar(100)I can't seem to get the following query to work. I want to return allNames and the sum of ALL work hours, in each row and order by eachINDIVIDUAL work hour:SELECT Name, SUM(WorkHours) as hFROM EmployersORDER BY WorkHours DESCIt seems that putting WorkHours in but the aggregate function and theORDER BY clause creates a problem.Thank you for your help!

View 2 Replies View Related

How To Re-order The ID Column Of A Table?

Oct 5, 2007

I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?

Thanks in advance.

View 6 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007



I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

View 1 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC



View 1 Replies View Related

Can You Use Column Order Value In Select Statement

Nov 6, 2005

Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View 2 Replies View Related







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