.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL MAX() function selecting ALL columns and not only those in the GROUP BY clause

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server

A school has classes. Each class has students. Each student may take up one or several courses.

One only table with thousands of rows contains the following columns (and some others):

  1. Student´s class
  2. Student id-number
  3. Student's course
  4. Student's grade (in the course)
  5. Some other rows. This table has about 25 rows

I need an only SELECT that brings ALL ROWS for each student's highest grade (grades are numbers, not letters).

In the example bellow, I have listed a fragment of the table and I have emphasized with bold and italic characters the rows that are supposed to be selected:

          Class       ID-number     Course     Grade     Row5   Row6   Row7... 

  1. Class-A         100001     Math&n

    View Complete Post

More Related Resource Links

Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


how to prepare WHERE clause in vertical columns data

here the sample table X with datatype againt each data..how to prepare below WHERE clause from table X, WHERE   S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'     and table X is, CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X  

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.

a function in the where clause?

I have this where clause that I use in so many different places, I wanted to see if it is possible to have it as a function or something so that in the case I have to ever change it, I won't have to go through 50+ queries to change it.   Select * From Table1 Where IsTableAvailable = 1 AND (IsTableBroken = 0 OR IsTableFixed = 1)    

Dynamic columns on row group page

Hi, I have a SSRS (2008 R2) report on an Olap data source. On rows I have Agents (as a row group), on columns I have Stores (as a column group) and a measure, let's say SeelOutValue. I want to see sales from agents to stores. I added a page break between each group instance on the Agent row group => one page for an Agent, but the column stores are not filtered by the Agent. I want to see on an Agent page only the stores with values. My question: can I filter the columns (Stores column group) for each Agent (row group)?  Example Dataset:             Store1 Store2 Store3 Store4 Store5  Agent1     1                   1                    1     Agent2     2         2 Agent 3  .... Now an each Agent page there are all 5 stores columns (on Agent1 page column Store2 and Store4 without data). It is possible to see on Agent1 page only Store1, Store3 and Store5 columns, on Agent2 page Store1 and Store2 columns ... ?   Thanks in advance for your help!

Unable to Merge columns outside Group ? Unnecessary Row height ?

HI , In a matrix tablix , I'm unable to merge columns outside the group . Heres a quick screen shot . http://img843.imageshack.us/img843/366/samplema.png The top 3 columns is what i meant [ Unable to Merge columns outside Group ]  and Row height of the first row ? Thanks in Advance !!Rajkumar Yelugu

group only selected columns of tablix

i have a simple tablix, with item in rows and sales per year in columns...so depending upon the data available, the columns will be shown..from year 2010 back to any number of years..                   year item    2010      2009    2008    current yr   current yr + last yr 123     50           80        10            50            50+80=130 abc      119          29       40         119          119+29 = 148 now, i want to have a calculated columns ( current year sales ie first column value), (last two year sales ie first two column sum) how to achieve this.

Entity Model- Add Function Import - selected stored procedure returns no columns

I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

FieldTypes for Multi Text and Person or Group Columns?


i am trying to insert an Item through List Web service.

i am able to insert, Text column fields. But unable to  insert an Image in Multi text line, and inserting dates for Date column,Person or Group  fields

Please suggest me on this

 XmlDocument doc = new XmlDocument();
           XmlElement batch = doc.CreateElement("Batch");
           batch.SetAttribute("OnError", "Continue");
           batch.SetAttribute("ListVersion", "1");
           batch.InnerXml = "<Method ID='1' Cmd='New'>" +
              "<Field Name='Title'>"+strTitle+"</Field>" +
               "<Field Name='MultiTextAnnouncement'>"+Announcement+"</Field>" +
                "<Field Name='StartDate' Type='DateTime'>" + DateTime.Now + "</Field>"

Order By clause in RowNumber function slowing down the performance of my query.



I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.






Display columns in matrix based on a group even if data don't exist in resultset


I am trying to create columns in a matrix for data that currently does not exist.  I created a matrix that groups on office name (ROWS) and Month of Appointment Date (Columns).

For example, the matrix currently looks like this.....

  January February March April

max() agreate function and having clause not working as expected


I wil try to describe below my issue, starting with the query i use.

SELECT     b.[id_training_MD],
FROM [training_MasterData] as a
inner join [training_Log] as b
    on a.id_training_MD = b.id_training_MD
inner join [training_Q_Log] as c
    on b.id_training_Q_Log = c.id_training_Q_Log
WHERE (a.[Category] = 'Networking')  and  (c.[employeeID] = '123')

group by b.[id_training_Q_Log],b.[id_training_MD],b.[trainingScore]   
having b.[id_training_Q_Log] = (select distinct max(b.[id_training_Q_Log]) FROM [training_Log])

My tables are:

CREATE TABLE [dbo].[Training_MasterData](
    [id_Training_MD] [int] IDENTITY(1,1) NOT NULL,
    [Category] [varchar](50) NOT NULL,
    [TrainingName] [varchar](100) NOT NULL

Example of data from table:
[id_Training_MD]    [Category]         [TrainingName]

Group by on external data columns



I created an external content type "Auction" from a SQL table. I added an external data column "Auction" to my document library and selected a few fields to be shown as additional information on "Auction". I would like to create a view where the documents are grouped by one of those additional fields, but the field I need is not shown in the view settings... Is it possible to sort/group on the additional columns comming from from my external data column?





Formatting -- Tucking the child columns under the group header:


I seem to have missed something somewhere.

How do you get a report organized like:


Rather than:


You can't be successful at this unless you're at least 1/2 a bubble off level.

Combine Column Group Columns Into One?


I have a dataset like this:



1........... 09-2010................25,000




I have a report that shows this data by pivoting on the month and giving the remaining amount, like this:

BID......Aug-2010 Remaining........Sep-2010 Remaining.......Oct-2010 Remaining........Nov-2010 Remaining



This all works just fine. However now, I want to take a date that's given, and make a "Past Due" column for anything less than the first of the month of that date.


I have this partially working, as I can filter my column group to be >= CDate(Month(Parameters!CurrentDate.Value) & "/01/" & Year(Parameters!CurrentDate.Value)).


This would give me this:

BID.......Oct-2010 Remaining........Nov-2010 Remaining

1.......... 25,000..............................100,000


How do I combine the previous months into one column? Like this:

BID......Past Due Remaining.......Oct-2010 Remaining........Nov-2

count number of columns in group



I need to count number of columns in group -

I have a matrix with SUM in the date area.

I have a group column (lats say 'city') and under it my columns (lats say 'street name');

Under each 'street name' I have cell with SUM function.

The problem is:

When The main group (city) is not expand I get the sum of all cities and what I want to get is an average .

So what I thought of was to divide that number in the number of streets under each city group.

How can I get that number?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend