Post New Web Links

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

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

why am i getting the error message when both ORDER BY items appear in the SELECT query?

Query

SELECT DISTINCT
B.CreatedDate,
BI.DateFrom,
	CONVERT(VARCHAR(8),BI.DateFrom ,3) as FFromDate,
	CAST(FLOOR( CAST( BI.DateFrom AS FLOAT ) ) AS DATETIME) as FromDate,
	CONVERT(VARCHAR(8),B.CreatedDate ,3) as FBookingDate,
	CAST(FLOOR( CAST( B.CreatedDate AS FLOAT ) ) AS DATETIME) as BookingDate,
	P.BookingRef,
	P.PaymentTypeCode,
	(C.Forename + CASE

View Complete Post

More Related Resource Links

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Hi, i am using a query to get DISTINCT hotels from my Database table sorted by Price and it returns me same hotel times of record found by Price if i use this Query

SELECT distinct(hotelname), availstatus, hoteladdress, hotelphonenumber, hotelpostalcode, destination, starrating, imageurl, hotelcode, TempSession, HotelType, MinimumNightRate from HproTemp where TempSession = '12-53-37' ORDER BY MinimumNightRate

and i get the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" When i remove MinimumNightRate from SELECT LIST.

Can anyone have any idea how may i resolve it..

Type in first few characters to select items in a large list quicker

is there a way that you can type in the first few characters of an item in a dropdown box eg if you type ‘rad’ in the dropdown box it will take you to the first item starting with ‘rad’.

Is it possible that you could type in rad to get you to radiator for example?

Use BDC data in list as multi-select column

I have a document library that uses values from another system for its metadata. Right now, we copy the values from the primary system into SharePoint so users can choose them. I want to find a way to use the BDC connection that I have set up to automatically pull those values. The problem is that the current metadata is set to multi-select. So, I have a file that is in the library and it can be used for multiple clients - clients is a column in my library and users can select many entries. When I set up a column to pull BDC data, it only allows single entry. Does anyone have any ideas?

select of X items in a single field

Hi! I've a select like this:select fieldA,fieldBfrom tablebut fieldB must be a list of items of another table:something like this:select fieldA, (select itemA from table2 where idref = table.id) AS fieldB from tableI need to show in fieldB a list of items (X records) returned from my second SELECT with a "-" for separate X fieldsI should show in fielB this:itemA - itemB - item C - item Dis it possible?Thanks

Insert a "Select User" row in Linq for a dropdown list

I'm new to Linq. I have searched and searched the web for a soluion, and can't find anything. I have a Linq query and I want to insert a row ("Select User") to the top before I pass it to the drop down list. I've been trying to use the Union but to now avail (it keeps telling me that my object doesn't support the Union method). My code, prior to attempting to inser a row, is very simple.public SelectList DropDown_Users() { var context = new VivarianDataContext(); var query = from t in context.AspnetUsers select new { t.UserId, t.LastName }; list = new SelectList(query.AsEnumerable(), "UserId", "LastName"); return list; }Now I try to insert a row and I found this on the internet and it seems to say that his solution will work. But it is filled with syntax errors. http://magicode.wordpress.com/2009/08/20/inserting-an-item-in-iqueryable-object-using-union-met

Can not select Forms Library for List Definition

Why can I not select a content type of Forms Library when creating a new List definition? I want to create a form library list definition that I can link to a InfoPath form that I have created?

people picker problem, with multiple select lose the list.

Hi guys I have a master table with configuration for reports. when I load a InfoPath form (2010) for new report I have a approvers that set ok , and I have Notifies (multiple people) when I load a value form master they left me in the field only the first value(people) and lose the others. Do you know if there any way to load a multiple people value in people picker control? Thank in advance Ramiro

select distinct best row based on a condition

Hi all . The application I'm working on only allowed that one user had one usergroup. I create a User_UserGroup table to allow a user to have n usergroups. My problem now is the following: User 'ablanco' belongs to UserGroup 'comercial' and 'financeira' If i execute this commands i get the following results: SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'comercial' http://img26.imageshack.us/i/49234843.png ----------------------------------------------------------------------------------- SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'financeira' http://img259.imageshack.us/i/32526938.png ----------------------------------------------------------------------------------- I now need to get distinct rows that belong to ablanco's Usergroups but if on one group the module is Enable=0 and on the other the same module is Enable=1 it should return the ont that has Enable = 1 The attempt i made without taking in consideration this 'Enable' part was the following: SELECT MP.* FROM [T_APP_ModulePermition] MP INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup INNER JOIN T_APP_User U ON UGD.UserId = U.UserId WHERE U.UserId='ablanco' But since i cant use DISTINCT.* i get all the 14 rows. If someone can help me on this i would be thankful.

Troubleshooting an XSL Select to another List in a Dataview.

I've done this before to join in some column from another List into a dataview. I've looked over the two list and this line of code for a few hours now. It's producing no output, when I know there are matches. <TD class="{$IDASV40H}"> <xsl:value-of select="/dsQueryResponse/Applicant/Rows/Row[@ID = current()/@ApplicantID]/@AName"/></TD> None of the colums or lists have been renamed. None of the columns have spaces in the names. I want to make sure I'm reading this right. do I need to define current()? Any bood way to troubleshoot this in Designer? any issue with trying to compare @ID? Thanks.

XSL Lookup to another List in a View. Joining in data from another List. xsl:value-of select=

I've been able to create a DataView with data joined from two SPÃÂ Lists. I did this using SPD, a linked Source, and doing joined subviews.ÃÂ The technique works nicely, but I have to create my DataView from scratch.As an alternative, I took an existing AllItems View in SPD, converted it to XSL, and was able to add a column from another List as follows:ÃÂ ÃÂ ÃÂ <TD Class="{$IDA1VDGH}" style="width: 62px; height: 23px"><xsl:value-of select="/dsQueryResponse/Master/Rows/Row/@Lastname" />The problem is, it reports the column for the first row everytime.ÃÂ I'm new to XSL and was wondering..ÃÂ ÃÂ ÃÂ using XSL, and preserving all the original columns already in the View, is there anyway to make the added column smarter, maybe with a filter?I noticed in my working example, I have this filter (I guess where the subview join magic is) ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ ÃÂ [@Title = $dvt_ParentRow/@Key] which does not resolve syntax:I tried this, but it does not work, only finding one match, and it also seems to get set for the entire Dataview and not just the column. ÃÂ ÃÂ ÃÂ [../../../Master/Rows/Row/@Title = @Key] I guess I'm looking either for the SPD steps, or XSL code for adding a single column from another list, joined in by a column in both lists.Thanks for any help or information.

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks, Sambath

Dropdown select message in item template gridview dropdwon list ???

Hi ....I have used gridview item template , which has two dropdown list .The first dropdown list is binded from database to the dropdown list I have using the below code , to show the select message in dropdown list before selecting any item from dropdown list.In first row its show me --Select-- message and my vaues.. if i select some value form dropdownlist and click add new button it will create a new row dynamic then in previous row again it come back --Select-- message... on going to second row....I need to remain first row user selected value on going to next row.The New row will show --select-- message..Please go to my code and let me know what to do<asp:DropDownList id="DropDonList2" tabIndex=11 runat="server" OnSelectedIndexChanged="DropDonList2_SelectedIndexChanged" AutoPostBack="True" DataTextField="SizeRange" DataValueField="SizeRange" OnDataBound="DropDonList2_DataBound" __designer:wfdid="w10" AppendDataBoundItems="True"> <asp:ListItem Value="-1">Select</asp:ListItem> </asp:DropDownList><asp:DropDownList id="DropDonList2" tabIndex=11 runat="server"

Select more than one row from an attribute list

Xquery newby! I was looking at the answers to the post "How do I get my xml in table form?" and have a further question.
Here is what I have.

create

table #xcap (id int, xml_col xml)

How To Remove Select in Jquery in List box

i crate a list box by database but select is show how to remove this using jquery


<td><select name="ListBox1" multiple="multiple" size="8" id="ListBox1">

		<option value="">Select</option>
		<option value="1001">1001.Ashok</option>
		<option value="1002">1002.Rajiv</option>
		<option value="1003">1003.Garima</option>
		<option value="1012">1012.Akhil</option>
		<option value="1011">1011.Nasir</option>

		<option value="1006">1006.Ashish</option>
		<option value="1004">1004.Tazeen</option>
		<option value=

order by sort both select in union

I have DropDownList that Show all groups. in that, I want to show "select one group" as default in DDL. for that i used code below. but it sort all return data and "select one group" will not be the first line. How can i fix it?

Best Regards. Morteza

	SELECT   -1 as id,'select one group' as groupName
	union
	SELECT   id, groupName
	FROM     [group] 
	order by groupNam

How to set items from Select Command in Data Source to populate textboxes

I need to know how to get the items from a Select Command in a Data Source to populate textboxes. For instance in the Select statement below I want to set the textbox for First Name equal to the item in the Select Command highlighted below:

 Protected Sub MHGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MHGrid.SelectedIndexChanged
        Me.Table1.Visible = True

        Me.MHFoundation.SelectCommand = "SELECT POPULATION_LIST_ID, FIRST_NAME, MIDDLE_NAME, SSN, BIRTH_DATE, ADDRESS, SPOUSE_NAME, OTHER_ID2, WORK_PHONE, PREFERRED_COMMUNICATION, LAST_NAME, NAME_PREFIX, NAME_SUFFIX, MRN1, CITY, STATE, ZIP, CHILDREN&,OTHER_ID1,POPULATION_TYPE,HOME_PHONE,CELL_PHONE,POPULATION_RATING,NOTES,EMAIL,PERSON_TYPE FROM CHP_POPULATION_LIST "
        Me.txtFirstName.Text = (1)

    End Sub

Re-Order List items not moveable

Hello,

I have been trying to get the Re-order list to work using the following tutorial (but in VB instead): http://weblogs.asp.net/justinsaraceno/archive/2008/02/22/reorderlist-with-objectdatasource.aspx

The list is displaying properly, but none of the items are moveable. My code is pretty much exactly the same as the tutorial.

Any help is appreciated


Categories:

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